Use of tenant_id in multi-tenant database

Question

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?

Answer 1

A schema in Postgresql is akin to a "database" in, say, MySQL; it is a namespace containing a distinct and separate collection of database objects (tables, views, stored procedures, triggers, etc). Creating a schema for each tenant will allow you to

• Hold a database connection open and switch between schemas,
• Query across multiple tenants using UNION, and
• Avoid problems where someone changes the ID and accesses the data of some other company.

If you create a schema for each company, you won't need company_id fields in each table; the schema will provide the necessary separation. Your ActiveRecord query will look something like company.Projects.find(3), where company is the name of the schema corresponding to that particular tenant.