In this article we will discuss one approach to creating a single database which could be used by multiple businesses (tenants). The most important design consideration is how to safely isolate the data between tenants, so that no tenant can see or influence in any way another tenant’s data.

One advantage of a multi-tenant system is that maintenance is reduced by having to maintain only one database which could be used by a large number of tenants.

Using Schemas to Partition the Design

In a multi-tenanted database design isolating each tenant’s data is going to be very important, we will do that by creating two sets of Schemas. Rather than creating tables on the dbo Schema (normal default Schema), SQL Server allows you to create a named Schema to hold tables and other design objects. Schemas let you partition the design, and are also extremely useful when managing security.

The first set of Schemas will all have names ending in _B suffix, for example: - Companies_B, Sales_B etc. These are the base schemas, and no user will have any permission on these Schemas.

The second set of Schemas will be named without the _B suffix, for example Companies, Sales etc. Users will have permissions on these Schemas and on each Schema we will create a View through which a user will interact with their data.

We will also create a synonym for each of the above views; this will enable any other views we have in the system to refer to the views without qualifying them with either the schema name or the ending _VW.

System Tables

We need to have a table which contains a list of the tenant businesses allowed to use the system. Below you can see an example of this table which would contain 1 row for each tenant. The field called CoCode holds the unique identifier for a tenant (Company Code).

For each user of the system we need to record which businesses data the user is entitled to see, this is recorded in a table which we call the parameter table (the actual name is tblParameters_M which was named so that it would be unlikely to conflict with the names of any application tables).

The ActiveCoCode fieldshown below indicates that the user works with tenant 1.

Filtering Data for each Tenant

We use the SUSER_SNAME() SQL Server function to get a user’s identity, and we have added additional string manipulations to remove the domain name.

SUBSTRING(SUSER_SNAME(),(CHARINDEX('\',SUSER_SNAME()) + 1),LEN(SUSER_SNAME()))

We then create a view which will only show the active users record from the parameter table.

CREATE VIEW [Reporting].[vw_tblParameters]
AS
SELECT * FROM [Reporting_B].[tblParameters_M]
WHERE [Parameters_UsernameFilter] =
   SUBSTRING(SUSER_SNAME(),(CHARINDEX('\',SUSER_SNAME()) + 1),LEN(SUSER_SNAME()))
WITH CHECK OPTION
GO

This view and all the other bottom level views that users works with have a WITH CHECK OPTION, this will prevent any user attempting by accident to insert data which cannot be reflected back through the view . This limits them to working only with their own businesses data.

The views through which the user will interact with the system, all have a similar structure:-

CREATE VIEW [Companies].[Shippers_VW]
AS
SELECT * FROM [Companies_B].[Shippers]
WHERE [CoCode] = (SELECT [ActiveCoCode] FROM [vw_tblParameters])
WITH CHECK OPTION
GO

Note: you would also want to take steps to ensure that a user is not able to change the value of their ActiveCoCode.

Base Table Structures

Each table in the system has a composite primary key, where the CoCode (unique tenant id) is part of the key.

ALTER TABLE [Sales_B].[Order Details] WITH CHECK ADD
CONSTRAINT [rel_OrdersOrderDetails] FOREIGN KEY([CoCode], [OrderID])
REFERENCES [Sales_B].[Orders] ([CoCode], [OrderID])
ON DELETE CASCADE
GO

Automatic Assignment of Tenant Identifier

When the application or a user inserts data into a table, they would need to know the value to insert for the CoCode field. To avoid the need for this we can design a function to get the CoCode for the user.

CREATE FUNCTION [dbo].[GetCompanyCode]()
RETURNS INT
AS
BEGIN
   Return (SELECT ActiveCoCode FROM Reporting.vw_tblParameters)
END
GO

We then add a default for the CoCode to all the base tables in the system, which uses the above function GetCompanyCode().

ALTER TABLE [Companies_B].[Shippers]
ADD
CONSTRAINT [def_Shippers_CoCode]
DEFAULT ([dbo].[GetCompanyCode]())
FOR [CoCode]
GO

Identity Columns and Multi-Tenanted Systems

Often you have a column with the Identity property set as part of the primary key on a table. Depending on whether these numbers are made visible by the application, you may want to consider the following potential issue.

Tenant 1 enters a sales order and sees a unique number 5006, a few days later they enter another sales order and see the next sequential unique number 6026. Because the automatic numbering is shared by all your tenants any given tenant they will not necessarily get a sequential series of numbers.

The easiest solution to this problem is to create your own table for holding unique sequenced numbers for each tenant, and then write some trigger code on each table to issue the sequential numbers for each tenant. You could display this new field in the application, instead of the existing field.

Note: Unfortunately we cannot use a function assigned as a default to achieve this.

CREATE TABLE [Admin_B].[tblRecordIDS](
[ID] [int]      IDENTITY(1,1) NOT NULL,
[CoCode]      INT NOT NULL,
[TableName]      VARCHAR(256) NULL,
[LastUsedId]      INT NULL,
[CommonName]      VARCHAR(255) NULL,
CONSTRAINT [pk_tblRecordIDS] PRIMARY KEY CLUSTERED
(
[CoCode] ASC,
[ID] ASC )
)
GO

INSERT INTO [Admin_B].[tblRecordIDS]
(CoCode,TableName,LastUsedId,CommonName)
VALUES(1,'Products',78,'Product Information')
GO

Then we add the new user interface key to the table, this has the same name as the existing field, but with a _UI suffix (User Interface).

ALTER TABLE [Products_B].[Products]
ADD ProductID_UI INT
GO

We now create a view and synonym for our table of unique identifiers.

CREATE VIEW [Admin].[tblRecordIDS_VW]
AS
SELECT * FROM [Admin_B].[tblRecordIDS]
WHERE [CoCode] = (SELECT [ActiveCoCode] FROM [vw_tblParameters])
GO

CREATE SYNONYM [dbo].[tblRecordIDS] FOR [Admin].[tblRecordIDS_VW]
GO

It is also useful to create a stored procedure which can be used to issue the new sequential numbers:-

CREATE PROC [dbo].[sp_GetUniqueKey]( @Tablename VARCHAR(128),
            @UniqueId INT OUTPUT)
AS
BEGIN
    DECLARE @NextId INT
    DECLARE @CoCode INT
    -- The synonym RecordIDS uses the user specific view RecordIDS_vw
    -- If not unique key exists then generate the key
    IF NOT EXISTS(SELECT LastUsedID FROM tblRecordIDS WHERE TableName = @TableName)
    BEGIN
        SET @CoCode = dbo.GetCompanyCode()
        INSERT INTO tblRecordIDS(CoCode,LastUsedID,TableName)
        VALUES(@CoCode,0,@TableName)
    END
    BEGIN TRY
        BEGIN TRAN
            -- use a dummy update to lock the record
            UPDATE tblRecordIDS
                SET LastUsedId = LastUsedId WHERE TableName = @TableName
            -- get last value
            SELECT @NextId = LastUsedId FROM tblRecordIDS
                WHERE TableName = @TableName
            -- increment value
            SET @NextId = @NextId + 1
            -- save the value
            UPDATE tblRecordIDS SET LastUsedId = @NextId
                WHERE TableName = @TableName
            -- commit the transaction
        COMMIT TRAN
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN
        SET @nextID = -1
    END CATCH
    -- return the id to calling program
    SET @UniqueId = @NextId
END
GO

We can then create a trigger on the Products table which will issue the sequential numbering.

CREATE TRIGGER [Products_B].[TR_Products_I]
ON [Products_B].[Products]
FOR INSERT
AS
BEGIN
    -- gets new unique ids for primary key from a table
    -- note we can not use a function for this
    -- use a cursor and stored procedure
    DECLARE @NextUniqueId INT
    DECLARE @PrimarykeyPart INT

    -- Define the cursor
    DECLARE InsertedData CURSOR FOR
        SELECT ProductID_UI
        FROM [Products_B].Products
        -- don’t use a join here
        WHERE [ProductID] = (SELECT [ProductID] FROM [INSERTED])
        FOR UPDATE OF ProductID_UI

    -- process all new records
    OPEN InsertedData
    FETCH NEXT
    FROM InsertedData INTO @PrimarykeyPart
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Get next unique id
        EXEC sp_GetUniqueKey ‘Products’ ,@NextUniqueId OUTPUT
        -- update the key
        UPDATE [Products_B].[Products]
        SET ProductID_UI = @NextUniqueId
        WHERE CURRENT OF InsertedData

        FETCH NEXT
        FROM InsertedData INTO @PrimarykeyPart
    END
    CLOSE InsertedData
    DEALLOCATE InsertedData
END
GO

We can then test what happens when a new record is added to the table (after we refresh the product view so we can see the new UI field):-

sp_refreshview 'Products.Products_VW'
go

INSERT INTO Products(ProductName)
VALUES('Test Product')
GO

SELECT CoCode, ProductID, ProductName, ProductID_UI
FROM Products
WHERE Productname = 'Test Product'

Although ProductID is sequential at the moment after different tenants have performed inserts it will no longer be sequential, but ProductID_UI will remain sequential.

Assigning Security with Database Roles and Schemas

One of the easiest methods for implementing security is to construct a database role, then grant permissions to the role. Note that the role will only have permissions on the Schemas containing views, and not any of the Schemas containing tables.

CREATE ROLE GeneralUsers
GO

GRANT SELECT, INSERT,UPDATE,DELETE, EXECUTE
ON SCHEMA ::Companies TO GeneralUsers
GO

When new users are added to the database, they are assigned to this role.

Summary

In this article we have shown one approach to creating a multi-tenanted database design, subsequent articles will address techniques for developing .net applications which utilise this design.

MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple databases and simplifying security. Click below to improve your development productivity.

Top of Page