06-06-15, 12:23 #1Registered User
- Join Date
- Jun 2015
Multi-client environment, how many databases?
I have a web application using SQL server 2008 R2 that is used by businesses to perform some business function they require. I expect to have no more than 1000 customers, but at the moment it is much less than that. Each customer has a unique account number with me. My question is what is the most appropriate way to store the customer's data, in a single large database keyed off their unique account number, or in individual databases named with their unique account number?
My instinct is one large database, but somehow intermingling different customers data seem wrong. Each customer could have thousands of their own clients and those clients could have hundreds of various transactions.
My concern with a single large database is possible slow query speed from large tables, being that in a database with 1000 customer's data, only about 0.1% of the records in the table actually apply to that customer. Also if a customer leaves and wants to take their data I would need to dump it somehow, whereas if it were in a standalone database I could make a backup for them (their IT guy) that they could restore.
However if each customer had their own database and I needed to modify it, I would need to do the change times the number of customers. I'm not sure of the practicality of managing hundreds of databases on a sql server.
So in cases of large online applications, like say Quickbooks online, is my data in its own database or am I intermingled with thousands of other users?
Last edited by ken411; 06-07-15 at 08:18. Reason: clarity
06-07-15, 09:17 #2Resident Curmudgeon
- Join Date
- Feb 2004
- In front of the computer
Go with a single database!
There are complications that you can encounter from having multiple clients co-mingled in a single database, but those problems are trivial in relation to the problems that you can run into with having multiple copies of very similar databases.
In the case of Quickbooks, you are in a single database with a single schema, but the design of that database keeps your data isolated from the data of other customers.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.