Designing a multi-tenant database for a scenario with multiple user types

Question

I'm developing a SaaS application for recruiting which has multiple user types such as Client, Recruiter, Panellist and Owner etc. There is also a chance that the user types may increase.

The Owner has an Organization and this is what will be my tenant. Here I'm considering every owner's organization as a tenant. I'm using Django to develop this application and will be utilizing the schemas feature provided in the PostgreSQL, hence there will be a schema for every tenant.

What I'm planning to do is that there will be a global User table that will be kept in the public schema and stores generic user information such a first name, last name, email, password, etc. The User table will be used for logging into the site. And there will be additional tables for each of the respective user types that each have their own unique set of columns. These additional tables will be tenant/organization specific.

Now once the user logs into to site, he can then switch to his desired tenant/organization something similar to how workspaces function in Slack. Within the tenant/organization, the user can assume the role of the above-mentioned user types that were allotted by the Owner to the user. So the user can be a Recruiter in one tenant and he can also be a Panellist in another tenant.

There will be an UserOrganization junction table which will keep track of the fact that a User can belong to multiple Organization.

Here is a schema diagram of what I'm thinking:

NOTE: I did not make the schema connections for the tables in the second tenant because it was starting to get messy but for all intents and purposes assume that they are present over there.

My questions are:

• What is exactly the best practice when dealing with multiple users in a multi-tenant scenario?

• What are the deficiencies of my design?

Answer 1

Using multiple schemas to implement a multi-tenant scenario is a feasible solution if you have few tenants. If you have too many tenants, you will end up with too many tables, and that will make life quite painful for you.

If you have many tenants, you could use one table for all tenants and use a column like tenant_id to identify them. If you don't want to trust to your application logic to restrict query results to the appropriate tenant, you could use row level security and let the database do it for you.

Another useful tool for the “single table” approach is partitioning. You can use list partitioning to split that large table in partitions based on the tenants. That way, you can bundle several (smaller) tenants together in a single partition. Partitioning will make it easier to delete all data of a single tenant (this is its main advantage), and it may save you an index on the tenant_id. Most queries won't get faster through partitioning though.

Answer 2

I’m in a similar situation right now, trying to make a decision and I’m kind paralyzed regarding going multi schema with one DB and shared schema. Anyway regarding your post, why have a separate table for each role? As you said the roles can increase which means you have to create additional table every time? I would go with one table and and a role column to differentiate the user roles or have a many to many setup since a user can have multiple roles and a role can belong to multiple users. Let me know if you implemented this and if so how was the experience with multi schema