Many tables for many users?

Posted by Seagull on Stack Overflow See other posts from Stack Overflow or by Seagull
Published on 2010-05-17T23:03:47Z Indexed on 2010/05/17 23:10 UTC
Read the original article Hit count: 194

I am new to web programming, so excuse the ignorance... ;-)

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

© Stack Overflow or respective owner

Related posts about sql

Related posts about ASP.NET