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