How would you design the tables which should isolate the data using
TenantId in a single table? I came up with two approaches, written down the has pros and cons with my little experience, but I would like to to ask opinion who have successfully implemented.
- Tenant details are stored in
- Customer is unique to each tenant and gets stored in
- Sales Header & Item details are tightly associated to
CREATE TABLE Tenant( TenantId SMALLINT IDENTITY(1,1) NOT NULL, TenantName VARCHAR(32) NOT NULL ) CREATE TABLE Customer( CustomerId INT IDENTITY(1,1), TenantId SMALLINT NOT NULL, CustomerName VARCHAR(32) NOT NULL )
- Save 2 bytes for each record from
SalesOrderItemtable by eliminating the
To query the Sales Order must always join the
Customer table to identify the
CREATE TABLE SalesOrder ( SalesOrderId INT IDENTITY(1,1), CustomerId INT NOT NULL ) CREATE TABLE SalesOrderItem ( SalesOrderItemId INT IDENTITY(1,1), SalesOrderId INT NOT NULL )
- Data is nicely segregated by
Waste extra 2 bytes for each record on both tables.
CREATE TABLE SalesOrder ( SalesOrderId INT IDENTITY(1,1), TenantId SMALLINT NOT NULL, CustomerId INT NOT NULL ) CREATE TABLE SalesOrderItem ( SalesOrderItemId INT IDENTITY(1,1), TenantId SMALLINT NOT NULL, SalesOrderId INT NOT NULL )
As you may notice the pros and cons are just reversed for each approach, however I may not be the experienced person to write down the pros and cons in details for each approach. It would be nice, if someone with a similar experience can highlight some things which am totally missing here.
I hope, as the Tenants increase in the future it would be good to create Partitions based on TenantId and move their data into other drives using FileGroup approach in