December 17, 2014 at 10:47 am
I have a database design question that I am seeking your advice. The way I see it I have two options. If you see other options please feel free to include those options in your response.
I am not sure of the pro's and con's of each option so I am seeking the advice of people with more experience than I have.
I have an application that I want to market to as many customers as I can. I am wondering if I am better off creating completely separate databases and tables for each customer or creating one database that includes a the customer ID in each of the database tables.
It may be easier to explain what I mean with the following fictitious example. In this fictitious application my database is used for schools to track courses, students and teachers. Please note that this example is not fully flushed out. I am providing enough information to make the point of my question as clear as I can.
The tables are:
Courses
Teachers
Students
As I get more schools to purchase my application would I be better off creating completely separate databases for each school or would I be better off with one database where the School ID was included in each of the tables.
Please include the reasoning behind your choice.
Thanks I really appreciate it.
December 17, 2014 at 11:11 am
Picture this .... you get 1 million schools ... 1 million databases to manage!
Would be much easier to manage one database.
I know there's many other points to consider, but from a management point of view, one database is much easier to manage.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 17, 2014 at 11:26 am
That was one of the issues that I have been pondering. While I would fall off my chair if I had a million customers, it would be a problem that I would like to have.
My concern with the one database approach is performance. I would hate to have one customer affected by another. I also worry about one other thing. If for some reason the one database is down, all customers are affected. If I have separate databases only the one customer would be affected.
I guess this is the reason I am looking for guidance.
Thanks
December 17, 2014 at 11:44 am
Valid points, but if your databases are all on the same server, one customer can still affect others.
As far as disk IO is concerned, there are things you can do with partitioning to reduce how one customer could affect others.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 17, 2014 at 11:49 am
There really isn't a right or wrong answer for this. Management is more difficult with multiple databases but there are certainly some advantages. With a single database you have to include an additional parameter for every single query to ensure you only get data for that customer. Another option to consider is if this is a web application you will need a new website for every single customer or get really creative with connection strings. Either path has benefits and challenges. The best decision is the one that best fits your situation. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 17, 2014 at 4:25 pm
Thank you both for you input. I will keep what you said in mind.
December 17, 2014 at 7:20 pm
Just to chime in here (meant to earlier, but was at work) on this.
First, Sean is correct, what is the best solution also depends on the situation.
Now, my thoughts.
Personally, unless there's an overriding business reason, I would generally lean towards one database per customer. You can either put everyone on a single instance of SQL or have multiple instances of SQL (one per customer.) If you start to run into performance issues, it's easier to migrate one or more databases to a new server.
Lastly, and this does go back to a business decision, who "owns" the data, the customer or you? If the customer owns it, if they at some point decide they want to leave your service, you hand them a backup file and disable their login(s). If you own the data, then this situation matters less.
Just my 2c
Jason
December 17, 2014 at 7:49 pm
Jason, thanks for the input. You bring up some good points. The ownership of the data is something I am going to have to consider when I make my decision. With that being said, even if I use one database I can provide each customer his/her own data.
December 18, 2014 at 12:31 am
Hi,
you should also think about being able to have your databases customizable as you'll often get special requirements from your customers. Therefore it could be helpfull, if you have more than one database, all having same "main" database scheme, standard stored procedures, views, etc. and additional functionality implemented in customer schema.
So you'll be able to also deliver a "standard" and put it on arbitrary database Servers.
Armin
December 18, 2014 at 1:41 am
The other thing to think about is that the database is the unit of restores. Will you ever be required to revert customer 42's data back to how it was yesterday 2pm without affecting the other customers? If so, that's a strong indication towards separate databases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 18, 2014 at 5:04 am
In cases like this, I am always strongly in favor for having a separate database for each customer, for the following reasons:
1. Easy to backup and restore the data of every customer as a separate unit. It will occur that a customer needs to restore a backup of a few days ago, and this is easy to do when each customer has their own database.
2. Security. You can handle security/logins for every customer separately, and you will not get into trouble if you (or a programmer) forgets a WHERE clause (or forgets parentheses around an OR clause, etc.) to select the data of a specific customer.
3. Performance tuning. You will have very small and very big customers, each with their own data-distribution. With separate databases you can tune indexes and stored procedures for every customer separately.
4. Scalability. With separate databases it's easy to scale horizontally by adding more servers and move a set of databases to the new servers.
Having many databases on a single SQL Server instance is not a problem, in general. I have servers running with more than 5000 databases, without any issues.
December 18, 2014 at 6:01 am
GilaMonster (12/18/2014)
The other thing to think about is that the database is the unit of restores. Will you ever be required to revert customer 42's data back to how it was yesterday 2pm without affecting the other customers? If so, that's a strong indication towards separate databases.
Thank you Gail, I'd forgotten that point!
Taking that a step further, while you could, in theory, restore a copy of the DB to recover *just* customer 42's data, how long would that take with one DB for all customers?
Imagine if your DB is around 7-800GB (or into the terabyte range) and you need to restore a copy to recover a measly 256MB of data (customer 42 is a smaller customer)
December 18, 2014 at 7:10 am
GilaMonster (12/18/2014)
The other thing to think about is that the database is the unit of restores. Will you ever be required to revert customer 42's data back to how it was yesterday 2pm without affecting the other customers? If so, that's a strong indication towards separate databases.
GilaMonster. this is yet another thing I hadn't considered. I am glad I asked my question all of you have given me valuable input.
Thanks
December 18, 2014 at 7:12 am
SebastianT (12/18/2014)
In cases like this, I am always strongly in favor for having a separate database for each customer, for the following reasons:1. Easy to backup and restore the data of every customer as a separate unit. It will occur that a customer needs to restore a backup of a few days ago, and this is easy to do when each customer has their own database.
2. Security. You can handle security/logins for every customer separately, and you will not get into trouble if you (or a programmer) forgets a WHERE clause (or forgets parentheses around an OR clause, etc.) to select the data of a specific customer.
3. Performance tuning. You will have very small and very big customers, each with their own data-distribution. With separate databases you can tune indexes and stored procedures for every customer separately.
4. Scalability. With separate databases it's easy to scale horizontally by adding more servers and move a set of databases to the new servers.
Having many databases on a single SQL Server instance is not a problem, in general. I have servers running with more than 5000 databases, without any issues.
SebastianT, thanks all good points.
December 18, 2014 at 10:03 am
For many of the reasons mentioned, I lean towards separate databases as well. However I also know that you often have many common elements, like procedures, functions, etc. that exist in all databases.
I'd consider a "master design" database that has these common elements on which you deploy your new changes and use as a test bed. I've seen people use replication from a master database to deploy changes, but since I may stagger deployments to clients, I think I prefer a good deployment system that allows me to specify and automate changes to all/any databases as I see fit.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply