Multitenant DB: Why put a TenantID column in every table?

Question

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?

Answer 1

If one of your key design considerations is security--specifically, one client can no way no how no when access another client's data--then, depending on how you implement this security, sticking that qualifying column in every table may be necessary. One such tactic described here requires building a view on every table; assuming each table contains a tenantId column, then if properly configured each view could contain a "WHERE tenantId = SUSER_SID()" clause (and of course you configure the database so that clients can only access the views).

Another factor (as in my current job) is loading warehouse data (ETL). Tables are partitioned on tenantId (we use table partitioning, but partitioned views would also work), and data can be easily loaded or unloaded for a client without seriously impacting any other client.

But as ever, there's a lot of "it depends" involved. If there is no clear and present need, and a very low likelihood of future need, then normalize that column out. Just realize that it's more a devise of physical implementation than of conceptual or logical database design.