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
.
Best Answer
I would go with including
Tenant_ID
in all tables. Yes, this is denormalized, but with good reason.In cases like this, it can be critical to ensure that one tenant cannot see another's data. If every query includes the
Tenant_ID
, that helps ensure this.Your note on future partitions is another reason (though not critical at this point).
I can think of one other. If one of your tenants required some sort of direct SQL access to the database, you could create views for them for all tables, with
WHERE Tenant_ID = 12
(or whatever the appropriate ID is, of course). By granting access to those views, and denying access to the actual tables, you would be able to provide the same security as you do with your application.