How should multi-tenancy be implemented for child resources in a SaaS database design?

Question

In SaaS multi-tenant database design, do you think it is sufficient to include the tenant id only in top-level resources, knowing that child resources will always be parent-scoped, or should every table include tenant_id as a foreign key?

For example, if Tenants have Projects that have Tasks, is it enough to specify Projects.tenant_id and only Tasks.project_id, because Tasks will "always" be fetched via their corresponding Project?

Or should Tasks (and every other table) include tenant_id without exception?

Thx!

Answer 1

If all code fetching data from the database is written correctly, then there is no need to have tenant id on each table. However, in practice bugs are inevitable, so there will at some point be improper data access in such a schema design.

By including tenant id on every table, you can set up row-level security at the database level so that improper application code cannot lead to data from a different tenant being disclosed accidentally. This is an example of defense in depth security architecture where there is no single point of failure.