Designing multi-company databases
I'm designing a website that can be used by multiple companies. It seems that there are two options:
1) The set of tables used by a company is owned by that company. This implies a database per company. This allows each company to upgrade individually but could cause managment problems with having many databases.
2) Use only one database but place a company ID column on each table. This implies only one database for the entire website but restricts companies from being able to upgrade independantly.
Are there any other options besides the two listed above? If not then which option do you think would be the best?
[723 byte] By [
prokewl] at [2007-12-25]
First of all, what do you mean by 'upgrade'?
Based on general assumptions, your requirement is probably for an Application Service Provision, wherein your app is used by various companies as if the application was theirs. On that pretext, I can say option #1 seems more appropriate. And option #2 is fundamentally/logically an incorrect design. (however practically it is correct).
#1 doesnt impose any restrictions, but yes, it does cause management overheads. On this I can say that there is no gain without pain.
Management can be streamlined by various ways, so dig into T-Sql with books online and you will surely find better ways to do it
Depending on the database. If you choose a mature database like SQL Server, i go for option 2. With connectionpooling you get fast DB interaction.
Based on general assumptions, your requirement is probably for an Application Service Provision, wherein your app is used by various companies as if the application was theirs. On that pretext, I can say option #1 seems more appropriate. And option #2 is fundamentally/logically an incorrect design. (however practically it is correct
Certainly both approaches are feasable (and there's a third one); a lot depends however on your security and extensibility needs.
You might want to check out the following article in MSDN: Multi-Tenant Data Architecture. It addresses exactly the kind of problem you're talking about and explains in a lot of detail each of the options available and the upsides and downsides of each one (including security implications).
