Search code examples
sqldatabaseprocessing-efficiency

Database efficiency - table per user vs. table of users

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?

Solution

  • 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.