SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 What is best approach to db creation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

thiemebr
Starting Member

2 Posts

Posted - 11/11/2012 :  20:53:41  Show Profile  Reply with Quote
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

USA
15635 Posts

Posted - 11/11/2012 :  21:25:51  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 11/11/2012 :  21:40:13  Show Profile  Reply with Quote
Not really. For such customer, create a separate DB and charge them more


KH
Time is always against us

Go to Top of Page

LoztInSpace
Aged Yak Warrior

938 Posts

Posted - 11/12/2012 :  20:30:06  Show Profile  Reply with Quote
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.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4351 Posts

Posted - 11/13/2012 :  13:03:47  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 11/13/2012 :  13:40:27  Show Profile  Visit robvolk's Homepage  Reply with Quote
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)
Go to Top of Page

LoztInSpace
Aged Yak Warrior

938 Posts

Posted - 11/13/2012 :  17:44:28  Show Profile  Reply with Quote
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.
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1596 Posts

Posted - 11/13/2012 :  18:15:16  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000