October 10, 2007 at 9:40 am
I would value input regarding a database design question. Our company is developing a new application and the purpose is take several of our current applications and combine them into one. Each customer may only use certain functionalities of the application. Most customers will not use all the functionality of the application.
The most important aspect of the new software must be that one customer absolutely cannot see another customer's data. This error has happened in the past due to training issues because of the central administration of user rights within the application.
The president would like to see separate databases for each customer because he feels this is the only way to ensure that the data from one customer cannot be seen by another. The most customers we will probably have at one time may be 20.
Another option would be separate databases for each module. As I mentioned every customer will pick certain functionalities that they will use and the functionalities can be grouped into modules. Currently the grouping of these functionalities is in separate applications and each application has it's own database. One could think of a group of functionalities as a module and the customer could then decide what modules they would like to use. Each module could be a database, but with this design we would have more than one customer's data in each module. There would be 5 modules.
Another requirement is that authentication must be central. Meaning there is administration needed where one person may need to see more than one customer's data.
Another issue is maintenance. There will be times where we will need to release new code to the application or to the databases. There could be cases where a customer does not use a current module, so they do not agree to the code upgrade. Therefore, we may need to keep track of different versions of the entire application if going to the customer database driven design.
To sum up the following are my options.
1. Separate database per customer. All functionality for the application and all modules will be included in each customer database, even if not used.
2. Separate database per module. More than one customer will be in each module and will be kept separate using unique ids. We will not
I'm interested to know what others have done in this situation.
Thanks for your help and please let me know if you need more information.
October 10, 2007 at 10:06 am
Yes and yes. Separate DB's / customer with separate DB's / module. Much more configurable and flexible. With 2K5, I might even suggest multiple instances - one / customer with each having their own setup. Sure, a bit more to administer but it would be an added layer of flexibility / security.
Keep in mind, though, that there are *always* chances for security breaches. especially since you have users that need to see multiple customers' data. Best bet is to have multiple checks in place and not rely on just one method.
October 10, 2007 at 11:06 am
Also - your "multiple UI version" argues for separate databases if the version change requires a change to the DDL.
I also don't like being on WHERE clause away from a disaster.
Totally agree - keep the data as separate as you can. Run separate instances; I'd even go so far as to say - run separate virtual servers (a.k.a VMWARE). That way - when one customer starts dragging performance down for everyone, it's easy to move their instance elsewhere without SQL or App reconfiguration.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 10, 2007 at 11:39 am
I'm largely piling on at this point, but what the heck...
Yes, seperate the databases by customer and seperate them by module as well. If possible isolate by instance or use virtual servers. I worked, once upon a time, for a system that combined everything, all customers, all modules into a single database. We had one huge customer and a bunch of smaller customers. The smaller customers were always complaining because the server was slow (usually corresponding to high loads from the main customer). What's worse though is that the various modules and their versions didn't work well together, so after a time we had to drop foriegn key constraints and make all kinds of fields nullable, killing our data integrity and the performance of the indexes. Horrible frigging mess.
Seperation has its own nightmares, but it will be scalable and flexible.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply