Multitenant DB: Why put a TenantID column in every table?
database-design, multi-tenant
Solution
If I had tenantID at the top of the hierarchy (i.e. at the zoo level) you have several issues to consider.
- The top of the hierarchy can never change, for example if you need to add a node on the tree above the zoo level (say regions -> zoos -> animals) then it will force a re-org every time.
- For certain queries, you will be forced to start at the top of the hierarchy, i.e. give me a list of all animals available will force you to start at the top of the tree
- Why not use schemas ? Each tenant is isolated within their own schema. This will also separate the data-sets nicely.
Problem
Every tutorial I've seen about Multitenant database models tells you to put the TenantID in every single table: ``` zoos ------- id zoo_name tenant_id animals ------- id zoo_id animal_name tenant_id ``` However, this seems redundant to me. Why not add the `tenant_id` column to just the `zoos` table and exploit the foreign key relationship between `zoos` and `animals`? Do you add `tenant_id` to every table just to keep the joins from getting too crazy? Is it a safeguard against bugs? A performance consideration?