Many tables for many users?

Question

I have a web application that in many ways can be considered to be a multi-tenant environment. By this I mean that each user of the application gets their own 'custom' environment, with absolutely no interaction between those users.

So far I have built the web application as a 'single user' environment. In other words, I haven't actually done anything to support multi-users, but only worked on the functionality I want from the app. Here is my problem... What's the best way to build a multi-user environment:

1. All users point to the same 'core' backend. In other words, I build the logic to separate users via appropriate SQL queries (eg. select * from table where user='123' and attribute='456').
2. Each user points to a unique tablespace, which is built separately as they join the system. In this case I would simply generate ALL the relevant SQL tables per user, with some sort of suffix for the user. (eg. now a query would look like 'select * from table_ where attribute ='456').

In short, it's a difference between "select * from table where USER=" and "select * from table_USER".

Answer 1

Creating tables dynamically is rather dirty and confusing. Additionally, if you have lots of users it'll be a complete chaos if you have tons of tables - especially if you need to change something in n tables instead of a single table.

--> Use one table and add some user_id column. With proper indexes this will be as fast or even faster than separate tables.

Answer 2

the first option is better.

In general tables should contain normalized data, you shouldn't duplicate the same table.

Also the 1st option is safer, as you don't need to grant the ability to create or drop real tables to the program