HiI try to design a database where multiple tenants share the same set of tables. I'd like to know if I'm on the right way. Here's sample DDL:CREATE TABLE MasterOne ( master_one_id INT IDENTITY, tenant_id SMALLINT, ... CONSTRAINT PK_MasterOne PRIMARY KEY (master_one_id, tenant_id))ALTER TABLE MasterOne ADD CONSTRAINT FK_MasterOne_Tenants FOREIGN KEY (tenant_id) REFERENCES Tenants (tenant_id)CREATE TABLE MasterTwo ( master_two_id INT IDENTITY, tenant_id SMALLINT, ... CONSTRAINT PK_MasterTwo PRIMARY KEY (master_two_id, tenant_id))ALTER TABLE MasterTwo ADD CONSTRAINT FK_MasterTwo_Tenants FOREIGN KEY (tenant_id) REFERENCES Tenants (tenant_id)
I know there is no need for the tenant_id column in the primary key, but let me explain: There is only one tenant_id field in the following Nodes table to only allow foreign key values from the master tables that share the same tenant.CREATE TABLE Data ( data_id INT IDENTITY, tenant_id SMALLINT, master_one_id INT, master_two_id INT, ... CONSTRAINT PK_Data PRIMARY KEY (data_id, tenant_id))ALTER TABLE Data ADD CONSTRAINT FK_Data_MasterOne FOREIGN KEY (master_one_id, tenant_id) REFERENCES MasterOne (master_one_id, tenant_id)ALTER TABLE Data ADD CONSTRAINT FK_Data_MasterTwo FOREIGN KEY (master_two_id, tenant_id) REFERENCES MasterTwo (master_two_id, tenant_id)
Does this pattern make any sense? Or has it any drawbacks other than the broader clustered index?(I've used a IDENTITY column to avoid page splits cause some of the tables probably will have high insert rates)ThanksThomas
Multi Tenancy
Posted - 2008-06-28 : 09:08:36