Monday, March 19, 2012

How many databases/codebases?

Hi,
I have a question about SQL Server 2005.

Suppose we have a hospitals application where the patients records and all other records are the stored. The solution is a hosting one. Here are three options:

  1. We have one single database for all institutions (Hospitals) then we need to keep an ID for every institution say institution ID. If we need to rollback the transactions for one institution how can we do that while not rolling back for others? Also, every institution may have slightly different business rules i.e. 80% of the rules and data is same for every institution where as 20% business rules and data may be specific to the institution e.g. if institution is located in Canada instead of US. What I meant to say, every instituion has some data this is not in the base database but we need to add this data specifically for this institution to the base database (i.e. 80% common part) What is the best way / recommendataion to resolve these issues? Another example could be taxes, i.e. tax system in Canada, US and EU is very much different. Can we handle it in the same code base?
  2. The 2nd option is that we have a separate database for every institution. This will give rise to maintenace issues e.g. if a bug is fixed for one customer then the same bugfix has to be fixed for every customer, say 4000 customers. Then it will be very difficult to maintain the code base.
  3. The third option could be that the base database is same for every institution and in addition to base database there is one additional database for every institution which has the 20% rules/data that vary. But this option may run into problems if the rule/data is implemented in base database already has rules for this type of transactions, there will be a conflict and we need to manage feature list for every customer and first see if you execute in the base database, if not then use the institution specific database. Again this will raise maintenence issues.


What is industry practice or recommendation and why? Is there any material on the web or on the microsoft web site that gives guidance on these / similar issues.
Your cooperation will be appreciated.
Thanks,
Paraclete

Start here:
http://msdn2.microsoft.com/en-us/library/ms151198.aspx
http://www.databaseanswers.org/data_models/index.htm

No comments:

Post a Comment