Multi-Tenant Design with Single DB & Single Schema approach using Sql Server

Question

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:

1. Save 2 bytes for each record from SalesOrder and SalesOrderItem table by eliminating the TenantId 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 in SalesOrder and SalesOrderItem.

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.

Answer 1

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.