| Author |
Topic |
|
thiemebr
Starting Member
2 Posts |
 Posted - 2012-11-11 : 20:53:41
|
| I'm working in a web project where we have many customers who will use the same db. My question is, the best approach would be to have a single db for all customers or multiple db's? (one for each customer).Thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-11-11 : 21:25:51
|
| One database should be sufficient for all customers, unless you have customers that demand absolute security of their data. And frankly, such customers are probably not worth having. |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-11-11 : 21:40:13
|
Not really. For such customer, create a separate DB and charge them more 
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-11-12 : 20:30:06
|
| I'd respectfully suggest that there are very good reasons to have multiple databases per customer. Whether they outweigh the benefits of not doing so is up to you and your situation. I will play devil's advocate to Rob's "don't do it".- Easier to code when everything you do does not have to join back to a "customer" table.- Security - one missed WHERE clause and you've mixed up customer data. Reputation & legal fees rarely recover from that.- Scalability - got a massive, power-hungry customer? Ship him out onto his own server. Can't do that so much with one box. (yeah I know you can, but it's not as easy).- Upgrade path. Especially with SaaS, sometimes some customers can't or won't upgrade all at the same time. Multiple databases allows a flexible upgrade path (watch out you don't end up with too many versions though).- Bye bye! When you lose a customer, removing or otherwise giving the customer their data back becomes a breeze. Not so much when it's all in one.- Some customers will demand separation anyway.got to go now, but there are some things to think about. I'll check back & update later if I think of any more. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-11-13 : 13:03:47
|
| All depends on the web app. If I had a Facebook account, I'd be a customer of theirs. Do you think they would create a new database for me? (yeah I know bad example). AS a more concert example, if you had a time entry system that supported multiple customers. You might create a different DB for each client or you might not. As LostInSpace notes there are advantages. However, you need to weigh those advantages with the disadvantages and your particular needs. I can only guess at the size of your application, but if it really important you should get a consultant or hire a system architect to help design/make these decisions. I suspect that there is no need to create separate DBs, at least at this point. But you could always architect is such a way that transitioning to separate DBs in the future would be easier. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-11-13 : 13:40:27
|
| There's always a balance, which is to design it so that everything can fit in one database (nothing dependent on separate DBs) and that there's nothing preventing multiple databases. If you need a separate DB you'd simply spin up a new, empty copy, and populate it for those customers.One significant reason to think of a single database (if not actually keep only one) is code and structure maintenance. Having multiple databases with slightly...or wildly...different structures/codebases quickly becomes impossible to maintain. (I'm only saying this based on past experience) It's very tempting for customers to ask for different features, upgrade paths, etc., but you do have a responsibility to yourself and your company to keep your environments manageable. If you go the separate database route and find you're unable to patch certain clients or databases because of breaking changes, I'd consider that the wrong choice (again, based on my past experience...YMMV) |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-11-13 : 17:44:28
|
| Yes, very true, Rob.@Thiemebr: In case I didn't make it clear, your goal should be to support a single product with an upgrade path through at most a couple of versions in action at any one time. Preferably only one. You certainly don't want a customisation job with a different schema/code per customer.My background is hosted, outsourced financial management products (applications) rather than Facebook/LOLCats type of site, where clearly there's a difference as Lamprey suggests. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-11-13 : 18:15:16
|
| One aspect to keep in mind when making this decision is the expected amount of data and processing per each customer. Would the load on the system from any one customer cause the system to become unresponsive? Can you anticipate the need to distibute to a different server in order to handle the workload. If so, having each customer in their own database makes migration easier.=================================================We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry |
|
|
|
|
What is best approach to db creation