Take the 2-minute tour ×

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

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

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

share|improve this answer
Being the fact that the database will have over 60 tables managing row level separation is going to be a nightmare. I think it will be seperate db's for each user. Is there any performance difference between 100 seperate databases on the one SQL Server box vs 100 user's data in one database on the one SQL Server? –  HackAndSlasher May 9 '11 at 1:03
Also scalability and portability is a huge factor which steers me more towards the seperate db per user. –  HackAndSlasher May 9 '11 at 1:04
@HackAndSlasher - In this case, it looks like you will be best served by a separate database per customer. It is a tough way to go for a small implementation but it will be easier to handle if you become larger. –  BiggsTRC May 9 '11 at 1:10
60 tables a nightmare? Childrens work, seriously. Most apps have way more than 60 tables and programmers get paid to make them work as they have to. This is SO Far from a nightmare... holiday is more like it. –  TomTom May 9 '11 at 4:34
add comment

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.

share|improve this answer
add comment

Your Answer


