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
Tenant
table. - Customer is unique to each tenant and gets stored in
Customer
table. - Sales Header & Item details are tightly associated to
Customer
.
Common tables
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
)
Approach 1:
Pros:
- Save 2 bytes for each record from
SalesOrder
andSalesOrderItem
table by eliminating theTenantId
column.
Cons:
To query the Sales Order must always join the Customer
table to identify the TenantId
.
CREATE TABLE SalesOrder (
SalesOrderId INT IDENTITY(1,1),
CustomerId INT NOT NULL
)
CREATE TABLE SalesOrderItem (
SalesOrderItemId INT IDENTITY(1,1),
SalesOrderId INT NOT NULL
)
Approach 2:
Pros:
- Data is nicely segregated by
TenantId
column inSalesOrder
andSalesOrderItem
.
Cons:
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 Sql Server
.