Multi Database, Multi Table or Single Database?

Hi everyone!

I’ve just started working on a new project which involves creating a web app that will be accessed by multiple companies. The way I see it, there are three ways to separate the data:[LIST=1]
[*]Separate databases for each company:

COMPANY A DATABASE

  • Users Table
  • Groups Table
  • Tasks Table

COMPANY B DATABASE

  • Users Table
  • Groups Table
  • Tasks Table
    [*]Separate tables in a single database:

ALL COMPANIES DATABASE

  • Comp A Users Table
  • Comp A Groups Table
  • Comp A Tasks Table
  • Comp B Users Table
  • Comp B Groups Table
  • Comp B Tasks Table
    [*]Single table in a single database:

ALL COMPANIES DATABASE

  • Users Table (Company ID for each entry)
  • Groups Table (Company ID for each entry)
  • Tasks Table (Company ID for each entry)
    [/LIST]The type of data that would be kept in the database would be commercially sensitive, confidential data so security is very important. This makes me lean towards have a separate tables or databases for each company - be is it necessary, or simply piece of mind knowing that everything is physically separate?

The complication is that there are times when a user might need access to data related to multiple companies (whether it be full access, or limited to certain items via a permission system).

For example, let’s say Company A and Company B are working together on something (either as completely separate entities or subsidiaries of a parent company), and a user at Company A wishes to assign a task to a user at Company B, or allow them to access some sort of collaborative system (subject to obvious approvals etc etc). Does having multiple tables/databases therefore make this significantly more difficult (and/or costly). eg. you couldn’t just query the tasks table and see what tasks are assigned to a user regardless of company, rather you would need to check each tasks table in each database (or each tasks table in the same database); there would appear to be separate see accounts in each database for the same person that would need to be reconciled somehow; and it might also more complicated in respect of updating and maintaining?

I’m interest to know what approach you might take in this sort of scenario. Is there a hybrid approach that might suit better?

Thanks in advance! :slight_smile:

I don’t think you’re improving security much unless you’re worried that one company may try to hack into another companies tables - in which case different databases, with different db user/password may give you some extra security.

If you’ve got the company ID check in place before dishing out the data then you should be okay with all data in one database with one set of tables imo. but INADBA (I’m not a database admin).

In the process of sharing you may end up with duplicate data and may effect integrity of the data based on just at a glance of what you’re doing…if you use multiple db/multiple tables method.

If the data is really really sensitive and there won’t be 100000+ companies using the application i think i would go with the unique database for each company solution.

That way you can never make the mistake of selecting the wrong table or row. The chances of selecting the wrong database by mistake are extremely slim i’d say.

And you get bonus points for not having to use bridge tables between users, groups and tasks and a companies table.

Database per company has some added advantages from a logistics and management perspective. First, if you get a big client, it is alot easier to move them to dedicated hardware and such if they need / want it. Second, when you have to customize things, you don’t need to worry about breaking everyone’s code. Finally, when you do have real customers you are not forced to upgrade everyone at once because of shared infrastructure.