Is it good practice to have relationship between the tenant table and all other tables in a multi tenant application?

Question

I am developing a multi tenant SaaS that uses Azure SQL for data storage. I make use of sp_set_session_context to make sure that any query only touches records relevant to the current tenant.

I occationally get requests to delete a tenant from the database. That means the record in the Tenants table and all other records in other tables with the same Tenant_Id. Today I do this with one DELETE statement per table.

Is it a good idea to create a relationship between Tenant and all other tables? This way I could use cascade delete to delete a tenant by simply removing a single record from the tenants table.

I am concerned about the more difficult recovery in the case of a mistakenly deleted tenant. I also want to be confident that there will be no serious performance issues with such a schema.

Answer 1

1. Is it a good idea to create a relationship between Tenant and all other tables?

   Yes. Absolutely yes, you should have a relationship between them physically (it is already there logically).

2. This way I could use cascade delete to delete a tenant by simply removing a single record from the tenants table.

   Er, um, not so sure about this one. Depending on the size / complexity of your schema, you might not be able to do this anyway due to potential circular references. I would do cascading delete FKs for subsets of tables that together act as a particular feature or property. But I don't see this as a good thing for doing over the entire schema, even if you don't get circular reference errors. Still, adding ON DELETE CASCADE to subsets of tables will at least reduce the total number of delete statements (although, you should already be doing the DELETEs in a transaction, so transactionally this won't really be any different; it will be fewer DELETE statements for you to code / maintain, though the main benefit will be helping to ensure integrity within those subsets).

For more info, please see my answer, here on DBA.SE, to the following question:

Composite Primary Key in multi-tenant SQL Server database