Hypothetical:



You are designing a product from the ground up. In the end you will have data for 20 different companies in said product. Do you have one database and add a customer ID to each table, or do you have 20 different databases?



I can see arguements for each scenario....



Opinions?




Keith Chisarik

It would depend on a lot of factors I would think. Here are some things I'd like to know:



1. Will there be analysis across companies? (argument for one db or one reporting db)

2. Will uses be granted access based on company or some set of companies? (more on this in a bit)

3. Is there need to share data between companies (i.e. while doing a task in company A, a lookup into company B is needed). If so, argument for one db, if not many would be OK

4. Will uses be spread out geographically, yet grouped geographically by company? I.e. users who access company A are geographically close, while those using company B are far from company A users but close to each other. This would suggest many dbs, physically located near users.

5. Will each company want control of there own data? I.e. could they want to host there own data. (argument for many dbs)



I'll say more on #2. If the users are going to access one db, but need access to some limited number of companies, you'll have to do some work with the RBS to get it to work to secure data at the row level based on the company they are working with. (See my posts, there a lot of them related to this issue). It can be done and it isn't too bad. If you are going to have many dbs, but allow users to access different companies, you'll just switch the DataSource. You might want to consider another database that would hold the mapping of users to company dbs.



Those are my first thoughts in any case.