none
Designing multi-company databases RRS feed

  • Question

  • 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?

     

    Monday, October 9, 2006 5:50 AM

Answers

  • 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).
    Monday, October 9, 2006 12:56 PM

All replies

  •  

    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

    Monday, October 9, 2006 9:08 AM
  •  

    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

    Monday, October 9, 2006 12:13 PM
  • 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).
    Monday, October 9, 2006 12:56 PM
  • By "upgrade" I mean that each client can choose when they wish their database to be upgraded when a new version of our software is released instead of automatically being forced to be upgraded with the new version of the software since their database is independent of one another.  If a single database was used for all clients then all of them would be forced to be upgraded at the same time.  This is problematic when the clients have installed client software on their local machines that communicate with the database.
    Tuesday, October 10, 2006 2:58 AM
  • Thanks for the link that's perfect.  Just what I was looking for regarding the pros/cons.
    Tuesday, October 10, 2006 2:59 AM