| Author |
Topic |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2009-10-06 : 10:31:09
|
| Hi,I am developing a system for several businesses i.e a, b, cEach business has various import feeds and the data in the import feeds get processed into similar tables for each business.I would like to create only one database which holds the data and the stored procedures of all the businesses.The stored procedures handle each business based on if statements, i.e. if business a, if business b, etc.I have been approached by one of the managers who seems to think it is best to have a database for each business. She says that all the databases will have to be similar and if half way down the development we realize that a field has to be added to a table in one business then the same should be applied to other businesses databses even if other businesses do not use that field.I did explain that this will be difficult to maintain for each database and the best thing is to have only one database and in the stored procedures, separate the logics for each business by if statement.But I guess the downside is that since there will be many business users, then there will be many hits onto one database as opposed to one database per business.Do you think the above is a valid reason to have only one database for this project?Thanks |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-06 : 10:47:19
|
No I don't think.In this case you have stored procedures with lot of IF ... code and that is not much better.If each business has its own db you can move it whenever you want.If all data for each business is in only one database it can happen by mistake that they get mixed in any way.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-10-06 : 10:59:37
|
| Webfred's arguments definately makes sense. Here is another view point:In my opinion one database is certainly worth considering. If you go that route then rather than IF statements I would probably add BusinessID as the first key in the clustered indexes of the appropriate tables. That would in effect partition the tables by business. The WHERE clause of your statements would limit to a particular business. I guess pertinent factors include maintenance, performance, security, and level of effort to modify all existing tables and code. The question of how to scale could be resolved by seperating businesses by database.Be One with the OptimizerTG |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2009-10-06 : 11:03:07
|
| Thanks guys.TG,Can you please elaborate on this:"I guess pertinent factors include maintenance, performance, security, and level of effort to modify all existing tables and code. The question of how to scale could be resolved by seperating businesses by database." |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-10-06 : 11:22:37
|
| Well what I mean is that no one can give you a definate "this is better" answer without knowing more about those areas I mentioned. ie:maintenance:you already stated that maintaining the same code accross many databases will be a maintenance headache and leaves room for things to get out of sync. However, perhaps you will decide that there are good reasons to have different versions per company.performance:if you have one business that has an extreme amount of data and users and that is causing a performance impact for the users of a small business then that could be a reason to seperate them. Although performance analysis and optimization is always a tricky thing. With talented people you can go a long way...security:you may want to be able to make the claim to your customers that their data is phisically isolated and there is no chance for either security breaches or accidental cross company corruption.level of effort:if you have the databases seperate currently and you have thousands of database objects that will need to be modified to change to a single database then the maintenance gain may be overshadowed by the work needed to change that structure.scale:hopefully, you will grow your business and you'll find that your current system is overly taxed. Architecture that scales nicely helps to solve that problem. There are many ways you can scale your system. I don't really think the scale argument applies here because you can have seperate servers with one set of companies using one server and another set using another for both scenarios.Be One with the OptimizerTG |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2009-10-06 : 12:23:53
|
| Thank you |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-10-06 : 12:52:32
|
| You're welcome - let us know which way you end up going...Be One with the OptimizerTG |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2009-10-07 : 17:27:12
|
| Very much like going towards having one DB per business.Thank you |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-10-07 : 18:28:20
|
| I'll throw in these 2 cents: if you KNOW you will never exceed 10-15 databases, then one DB per business shouldn't be too problematic.But if you think you'll exceed 15, reconsider one database, or having multiple businesses per DB.Here's some reasons why, based on my experience:- Code updates and deployments will be a bigger nightmare than you think, ESPECIALLY if your features differ per DB. Once they get too far apart, you will NEVER, and I mean NEVER, reconcile them. You'll end up supporting 20 different code bases and DB schemas, even if they're 95% identical. We had a senior DBA (almost 20 years experience) quit over this.- Reporting across all businesses/DBs will become annoying very quickly. If you have a data warehouse, your ETL will have to run multiple times. No big deal if the DB structures are identical, but once they diverge, you'll cry. Our current data architect is crying over this even though the databases are identical. And yes, someone WILL want reporting data across all databases, and you WILL want to use a data warehouse instead of enumerating the DBs each time.- Development will be harder because each business/client will have separate developers or teams, and at some point they won't communicate even though they're working on the same features. Nothing is more enjoyable than debugging 10 different stored procedures that do almost the same thing but not quite.I will say this for multiple databases: I am so adept at using sp_msforeachdb now I can literally write it with my eyes closed. Other than that, it's been hell on earth, and I don't even administer the division with 20 different code bases (no one does, in fact) |
|
|
|
|
No of databases