Possible Duplicate:
Is that a good idea to use one database for 50.000+ shops?

We are developing an internet application in C# with MVC 4. It's connected to a database which will contain a companies customers and their turnovers, also the products.

Naturally,these companies want their data secured, and also the data needs to be brought from the database to the web application as quickly as possible.

We are unsure what is the most secure and performance effective approach:

  1. Have one smaller database for each company, containing their customer data. This way the only "weak" human link is to code the connection string correct depending on who's logging in.

  2. Use one single database containing every companies data and all their customer data. This will naturally be a very much larger database, and possible(?) more room for error, leading to one employee seeing another companies turnovers. A critical situation.

What solution might be most secure, and the fastest in crunching numbers and processing it to the web application? is this a common "security" solution, to minimize human error by giving each company their own database?

marked as duplicate by Aaron Bertrand, JNK Jan 29 '13 at 21:56

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

migrated from stackoverflow.com Jan 29 '13 at 21:47

This question came from our site for professional and enthusiast programmers.

  • Which DBMS are you using? Oracle? Postgres? – a_horse_with_no_name Jan 29 '13 at 19:16
  • MSSQL. thinking of using .sdf for each company database – user1100931 Jan 29 '13 at 19:34
  • I think this is about 95% or better overlap with the existing question that has a very thorough answer. If you want more details on your specific situation feel free to comment there! – JNK Jan 29 '13 at 21:56

Separate databases are preferable for many reasons. THese are the ones I gave when we had the same decision to make:

  1. Separating client databases means there is no way someone can manipulate a querystring to view another client's data.
  2. If client1 somehow nukes their database, Client2 through Client-n isn't affected and rollback only affects a single client.
  3. Better performance because each database has it's own separate file with its own dedicated IO, cursors, etc.
  4. Smaller tables are quicker to access. This may not be an issue now, but five years down the line when you have 10 10GB databases, you'll be glad you don't have only one 100GB database.
  5. By separating the databases, you remove the need for one whole layer of management. You don't need to check whether a request for data is allowed based on the source client and the targer client's data. This speeds up the data access even more.
  6. Backups are much more simple. What would you do if Client3 asks you to create a backup of their data? You'll need to write some serious SQL-fu if all the data is in one database. Having separate db's for each client means you can simply use SSMS to run a quick export. Onle a few clicks, a quick ZIPm and the backup is ready for delivery.
  • 1
    You have some valid points but a 100GB database will be peanuts in 5 years. – ypercubeᵀᴹ Jan 29 '13 at 21:43
  • @ypercube Yes, but it's not now, and there will always exist an amount of data that isn't peanuts. Five years from now 100GB might be small potatoes because storage media have advanced, but for the past 30+ years storage demand has kept up with storage supply. There's no reason to think that the situation in 10 years won't be 10 100GB databases or 1 1TB DB. Or 1 10TB DB. When computers are capable of processing 1TB of data easily, people will find ways to collect 1TB worth of data. – Bacon Bits Jan 30 '13 at 1:22
  • THe point being that it is much faster to query a table with 10 000 records than one with 1 million, etc. The first instance won't require cursors for instance. – Captain Kenpachi Jan 30 '13 at 7:28
  • I don't get the comment about cursors. What does the use of cursors have to do with the size of tables? – ypercubeᵀᴹ Jan 30 '13 at 7:52
  • If you're accessing large tables, you'll probably want to use cursors. – Captain Kenpachi Jan 30 '13 at 7:57

Your security requirements seem to trump your performance concerns (otherwise you wouldn't call this part "critical").

Some general comments (but please see more details here):

  1. Keeping the data separate seems paramount. There is no weak human link with regard to connection strings. You store the client information in a central database, including what server/database to connect to, and the application code / middle tier builds the connection string depending on the client chosen (which can be tied to credentials, etc).
  2. Performance wise it is going to be much more efficient to search a table for one customer's data than to search a massive table that contains everyone's data. You can also very easily split up 50,000 clients with their own databases across many servers instead of trying to do that all in one database in one instance on one server. Very difficult to scale out both read/write with one single monolithic database.
  3. Separate databases afford you the ability to place different customers on different SLA (e.g. their database can be on faster storage) and HA/DR (full recovery vs. simple, less data to mirror / replicate / log ship etc., different frequency of log backups, etc). They also allow you to recover one client to a point in time without also rolling back everyone else to that same point.

However, I don't know where you're going with the .sdf approach. For 50,000 databases you should have proper instances of SQL Server with real databases attached to those instances, none of this file-based compact edition nonsense. IMHO.