THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Buck Woody

Carpe Datum!

More Tables or More Databases?

I got an e-mail from someone that has an interesting situation. He has 15,000 customers, and he asks if he should have a database for their data per customer. Without a LOT more data it’s impossible to say, of course, but there are some general concepts to keep in mind.

Whenever you’re segmenting data, it’s all about boundary choices. You have not only boundaries around how big the data will get, but things like how many objects (tables, stored procedures and so on) that will be involved, if there are any cross-sections of data (do they share location or product information) and – very important – what are the security requirements?

From the answer to these types of questions, you now have the choice of making multiple tables in a single database, or using multiple databases. A database carries some overhead – it needs a certain amount of memory for locking and so on. But it has a very clean boundary – everything from objects to security can be kept apart. Having multiple users in the same database is possible as well, using things like a Schema. But keeping 15,000 schemas can be challenging as well.

My recommendation in complex situations like this is similar to a post on decisions that I did earlier – I lay out the choices on a spreadsheet in rows, and then my requirements at the top in the columns. I  give each choice a number based on how well it meets each requirement. At the end, the highest number wins.

And many times it’s a mix – perhaps this person could segment customers into larger regions or districts or products, in a database. Within that database might be multiple schemas for the customers. Of course, he needs to query across all customers, that becomes another requirement.

Published Wednesday, May 19, 2010 5:37 AM by BuckWoody
Filed under: , , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Cade Roux said:

I typically give this advice:

If you design in multi-tenant from the beginning, you can always have several multi-tenant databases - it's a little harder to retrofit that onto a database design.  If you have some huge customers, they can be in their own databases and then you can aggregate smaller customers into databases together.  Retrofitting that is a little easier, because typically, you can handle that in the connection just like any database switch.

You still need some way of routing customers to the correct database, and this can be done by user in a single "master" database or in some other directory services like Active Directory.

In this situation, it is good to eschew IDENTITY primary keys, since forklifting data between databases requires a UNIQUEIDENTIFIER (NEWSEQUENTIALID) to avoid having to handle changes to the keys for referential integrity.

May 19, 2010 10:55 AM
 

jon said:

Maybe 1 Database per "group" would work.  So, your answer is hybrid.

May 20, 2010 3:56 PM

Leave a Comment

(required) 
(required) 
Submit

About BuckWoody

http://buckwoody.com/BResume.html

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement