Database efficiency - table per user vs. table of users

Question

For a website having users. Each user having the ability to create any amount of, we'll call it "posts":

Efficiency-wise - is it better to create one table for all of the posts, saving the user-id of the user which created the post, for each post - OR creating a different separate table for each user and putting there just the posts created by that user?

Answer 1

The database layout should not change when you add more data to it, so the user data should definitely be in one table.

Also:

• Having multiple tables means that you have to create queries dynamically.

• The cached query plan for one table won't be used for any other of the tables.

• Having a lot of data in one table doesn't affect performance much, but having a lot of tables does.

• If you want to add an index to the table to make queries faster, it's a lot easier to do on a single table.

Answer 2

Well to answer the specific question: In terms of efficiency of querying, it will always be better to have small tables, hence a table per user is likely to be the most efficient.

However, unless you have a lot of posts and users, this is not likely to matter. Even with millions of rows, you will get good performance with a well-placed index.

I would strongly advise against the table-per-user strategy, because it adds a lot of complexity to your solution. How would you query when you need to find, say, users that have posted on a subject within the year ?

Optimize when you need to. Not because you think/are afraid something will be slow. (And even if you need to optimize, there will be easier options than table-per-user)

Answer 3

Schemas with a varying number of tables are, generally, bad. Use one single table for your posts.

Answer 4

If performance is a concern, you should learn about database indexes. While indexes is not part of the SQL standard, nearly all databases support them to help improve performance.

I recommend that you create a single table for all users' posts and then add an indexes to this table to improve the performance of searching. For example you can add an index on the user column so that you can quickly find all posts for a given user. You may also want to consider adding other indexes, depending on your application's requirements.