Advantages and Disadvantages of Creating New Tables for Each User

databasedatabase-designpostgres

I'm working/playing on an app where individual users' data will be completely independent from each other. Of course, there will be common tables for other parts of the app (e.g. "users"), but for the rest of the tables, would it be wise to create a new set of tables for each user?

e.g.

List of tables:
users
user1_transactions, user1_items, user1_tags
user2_transactions, user2_items, user2_tags

for a total of 7 tables for the 2 users.

Would I have trouble with migrations with this setup (assuming the condition that user data will always be independent)?

I don't have much experience with relational design, so I guess this is a "I don't know what I don't know" situation. Is this a bad idea?

Best Answer

It's a terrible idea to be honest and there are a handful of immediate issues with that I can think of off the top of my head:-

  • modifying the table structure becomes very difficult because of the number of tables you need to modify.
  • you have to grant rights the app to change the table structure which is opposite to the principle of 'least privilege'.
  • you're mixing your data structure with your actual data which goes against the separation of concerns and mixes the design with the data.
  • migration shouldn't be a major concern although you do need to make sure that table names are unique.

That's all before we get into any issues of performance or limits on the number of tables or consider the impacts of how Indexing, and Views which will be useless across these multiple tables.

You're better off sticking with:

Users
Transactions
Items
Tags

You might find some useful information about designing your tables properly in Is it necessary to create a database with as few tables as possible

Related Topic