We are building a SASS web application where every company will be using its own database schema. Using postgresql, there is a table 'companies' in the public schema that holds the name of the company and the name of the schema to switch to. In the tenant schema there are a lot of tables that store data specific to this company. Every request, the tenant schema is set based upon the current company as stored in a session.
The internal discussion is this one:
Should the tables in the tenant schema have a company_id
column to denote the relation between the data and the company? Or is the current company implied by the used schema, and is it overkill to use the company_id
in every table that has a relation with a company?
Opinion 1
The first opinion is that the relations between the company and other tables should be defined explicitly in the table structure. This also enables ActiveRecord queries like: current_company.projects.find(3)
. Referential integrity is enforced, even for relations that cross the schema boundary.
Opinion 2
The second opinion is that it would be better to drop all company_id
columns in all tables because when you switch to a tenant schema, you will never be able to get data from other companies. The same ActiveRecord query must now be written as Project.find(3)
. The company is implied by the chosen schema, not through a relation. Also, dropping the company_id
column from every table would save disk space.
Which approach do you think is best?