The first question is: What is a Tenant and what is Multitenancy? Think of a tenant as a group of users, that operates independently on the same shared infrastructure. So to support multitenancy we have to logically isolate the tenants from each other while keeping them on the same infrastructure.
The support for multitenancy can be achieved in multiple ways, whereas the level of separation is the defining characteristic. Below I explain how multitenancy can be achieved on the database level. I won’t go much into the application level, because once the database infrastructure is done, the resolving of tenants and the isolation should happen automatically without thinking about it in the application.
Let’s have a look at the database. We will notice, a database has a hierarchy. A Server can have one or more databases. A database can have one or more schemas, which in turn contains one or more tables. The hierarchy can be visualized like this:
server.database.schema.table
On each level of the hierarchy, you can introduce multitenancy. I won’t talk about servers, because then we have dedicated infrastructure and don’t talk about multitenancy anymore. Let’s go through the others one by one:
You achieve the best isolation when separating tenants on the database level. This means every tenant has its own database. The Tenant information then contains a connection string for its own database, which is different from all the others.
You get the second-best isolation when the separation is based on the schema. A schema is like a namespace or a folder. (Yes, I know, it can do much more for you, but for the sake of simplicity). Every Tenant gets its own namespace within the database and is logically isolated by it.
You achieve the lowest level of isolation when separating on the table level. The idea is, that the data of all tenants is in the same table, but the table has an additional row for a unique tenant ID. Based on the tenant ID, only the data from a specific tenant gets selected.
Each variant has its tradeoffs you have to look at when determining which solution is the best for you.
- Do you need Isolation, then go for the database level tenancy.
- Do you expect lots of data and big tables? Better not choose the table level.
- Are you looking for simplicity? Then the table level could be the thing for you.
- Do you want to reduce the overhead by managing many databases? Don’t choose the database level separation.
- Looking for a middle ground? Maybe schema-based tenancy is the best choice for you.
Do you use multitenancy? How did you implement it? Let me know by sending me an E-Mail.