Im creating an IPad Application which will have an SQL Server backend. My question is, is it better for me to create a new database for each customer as they sign up or should I create an SQL script that generates new tables for the customer when they sign up.
e.g. Scenario 1 One database per user. For 100 users the sql server will have 100 seperate db's. i.e User1_DB, User2_DB......
Scenario 2 New set of tables for each User. For 100 users the sql server will have 100 seperate sets of the tables in the same database. e.g. MyIPad Database - Table1_User1, Table2_User1, Table1_User2, Table2_User2
In both scenarios we will have automated scripts that would generate the tables/Database when the user signs up.
So my question is what scenario is more efficient? Or are both scenario's wrong, therefore how should i approach this issue?
Thanks in advance
I would say that unless you have an explicit need to have this much separation, I would suggest having one set of tables and using a customer table with a customer ID to differentiate customer data. You can still limit access to the database through stored procedures so that no customer can see another customer's data but you don't have to deal with all of the overhead of multiple databases or thousands of tables.
If you must have separation, though, use different databases. Database separation gives you the ability to have more control over data security. If you have to go to the lengths of using different objects for each customer, you want the full security of separate databases. This will also give you the portability necessary for moving one customer to a different server, etc.
There is a similar question on SO already, and they followed my basic advice. Basically, Wordpress started out with one database but as they scaled up they ended up going with one database per customer so that they could scale out as well. Here is the link:
PHP Web Application: mysql database design best practices question
I wouldn't go this route at all, but rather do it at the row level, with each row of each table being tied to a specific user (or company/user, if the app can be registered for a particular company with many users). Creating separate tables/databases is simply not scalable for any significant number of users.
