Sql-server – Is it good practice to have relationship between the tenant table and all other tables in a multi tenant application

azure-sql-databasedatabase-designmulti-tenantsql server

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.

Best Answer

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).
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

Related Solutions

Composite Primary Key column order

Store the tenant_id first. When you do this you can enable index key compression.

See http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#i1106790 for the syntax and http://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm#i14618 for the concepts.

In your case, you can do it like this:

create unique index mytable_idx on mytable(tenant_id,id) compress 1;

alter table mytable add constraint mytable_pk primary key(tenant_id, id);

Postgresql – Multi-tenant database constraints

This is what I ended up going with:

create table blog_posts (
    id uuid not null,
    tenant_id uuid not null,
    title text not null,
    content text not null,
    primary key (id)
);

create unique index blog_posts_tenant_id_id_idx on blog_posts (tenant_id, id);

create table comments (
    id uuid not null,
    tenant_id uuid not null,
    blog_post_id uuid not null references blog_posts (id),
    content text not null,
    primary key (id)
);

alter table comments add foreign key (tenant_id, blog_post_id)
    references blog_posts (tenant_id, id)
    on update cascade;

Best Answer

Related Solutions

Composite Primary Key column order

Postgresql – Multi-tenant database constraints

Related Question