One Database vs. Multiple Databases

Question

I need to design a system which represents multiple "projects", one per client in SQL Server , something similar to StackExchange... same data model, different sites (one per customer). Each project has the same data model, but is independent of all others. My inclination is to use one database to store all projects. What is your recommendation?

Solution 1

I would make separate databases, because otherwise, if each client is using a similar schema, you're going to either have to combine tables or use lots of prefixes, or have link tables containing client identifying information. In addition, it will be much easier to manage backups/restores of client data if they have their own DBs.

There's really no good reason not to use separate DBs, IMO.

Solution 2

One downside of seperate db's is rolling out schema changes; if you have a couple hundred db's, be prepared to find a clever way to push new tables, stored procedures, indexes for upgrades to them. Another downside is mirroring becomes less attractive for a DR solution as the number of db's grows.

Solution 3

There's no right answer here. The purist-dba route would be all in one DB. If you're good at the design/config/sql you'd be amazed what you can get out of one modern day ~$8K server. The cheap and dirty web-scaling route would be 1:1 customer:db. That way if a customer gets wildly more popular than others you can split them out and scale their setup. The purist web developer route would be "one" db but built in a bigtable/dynamo kind of way to scale wide.

It really becomes less of a tech question and more of a business one. How many devs and admins do you have now, how talented are they, what do they already know, what kind of traffic do you expect in the very near term, what kind of revenue would it take to buy/lease 10x the hardware it takes for that, etc.

Overall thats why most of the older startup hands will say "just get it working and focus".

edit: also, there's no way this is a sysadmin asking the question. devs always like to think of designs that 10x the server config complexity "just in case".

Solution 4

I don't use MS SQL but as a principle, one DB per customer. Much easier to deal with. Plus, if one database gets screwed, and it does happen, it won't affect your other customers. Any maintenance procedures can be scripted for multiple databases as easily as one.

Solution 5

Separate DBs. In addition to other reasons given:

1. Your queries would otherwise need to all include a " AND project_id = $project_id"
2. Managing confidentiality, storing records of individual customers etc is much easier (you may want to periodically archive certain customers' db's)
3. If you ever do succomb to adding features for individual customers, you can add the schema changes just for that customer (or at least you have the option)
4. No danger of data leaking from one customer to another

Basically, it's much cleaner or more correct: if the rows in a table have absolutely nothing to do with each other, they shouldn't be in a table together. (Ok, I just made that rule up.)

View more solutions

Comments

[Ricardo Sanchez]

Good point about the backups/restore, thanks for the advice.

[Ricardo Sanchez]

You are absolutely correct about moving data easier by having separate databases and having the ability to take one client's db and set it up in a different server if needed.

[Ricardo Sanchez]

Thanks for your input. Wouldn't be just a matter of running the same scripts in all databases when a change in the schema is needed? I'm just guessing... I haven't have the need to do this yet but does not seem as a difficult task, but I could be completely wrong and that is the reason I am asking :)

[0100110010101]

Yes, but, that can be a daunting task when the numbers get big. And when things get out of sync, it's a royal pain, scripts start failing. If your db is nice and stable though, it might not become an issue.

[Ricardo Sanchez]

If I end up with 100,000 clients, I am sure I'll have enough resources ($$) to throw at the problem then... :) I see your point, however I am inclined to believe that one db per customer is better for all the reasons posted here by others. I will also think that with that many databases I would probably spread them to multiple SQL server instances. Thanks for your input!