Can we use shared schema multi tenancy(using tenant_id column to scope) combined with separate schema approach

Question

I am working on a ruby on rails learner project that uses multi-tenancy. This project has states(tenant) and cities(sub-tenants). User can only belong to one city. For first level i.e for states I have decided to go with separate schema approach using the Apartment Gem. Now my question is can I use table level multi-tenancy(using tenant_id column to scope) to implement subtenants(cities) under each schema. Are there any potholes which I am missing to see with this approach?

Answer 1

I don't know much about Apartment gem and its ramification on your DB design, but I know you can do the table level multi-tenancy and it's perfectly fine.

Adding a tenant_id to your cities table will allow you to fetch all the cities that belong to a particular state without any downside.

If I am right, I assume that is what you want to do?

Answer 2

Define your requirements. The answer to your question is the answer to this question:

How bad is it if someone saw data that they were not allowed to see? (i.e. they saw data that belongs to another tenant).

In other words, the answer is whether it is an acceptable risk to accidentally cross-contaminate data because of a malformed query or not? The answer isn't always a catastrophic failure.

Bottom line is for some industries, the answer to that question has regulatory impact (i.e. you would be in violation of some law). For others, you cause your customers to loose faith in the solution. Neither are great outcomes, but you have to consider the cost.

Here are the risks:

• Difference in blast radius: complex queries can affect the performance of other tenants, or if data leaks you can lose customers, or worse be in violation of laws.
• Difference in cost: it is always lower cost to keep all your data in as few buckets as possible, but separating it out into one bucket per tenant is always safer.
• How good is your testing?: you need to make sure your automated testing ensures that data is only returned for the calling tenant
• Difference in deployment: the more buckets you split your data in to, the more complex your deployments become. If your deployments are automated and sure, then that won't be a major issue

Bottom line:

• Separate databases are always safer for multi-tenant applications
• Separate databases are also almost always more expensive (depending on your hosting solution)

If this is a demonstration just to see the art of the possible: just use one database and use the tenant_id to differentiate the data. Just understand that might need to change for production.