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:
- Separate databases for each company:
- Separate tables in a single database:
- Single table in a single database:
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!