The Invisible Database

Previous posts (Part 1, Part 2, Part 3, and Part 4) in this series outlined the factors to consider and 4 architectures to consider when implementing a multi-tenant database system. Each of the previously described architectures kept one tenant’s data isolated from other tenants, either by storing it in completely separate databases, or in separate schemas. The last model to discuss is the Shared Schema model, where all tenants share a single schema, and a given table will have data from multiple tenants intermixed.

Shared Schema
In this architecture, all data is stored in one set of tables. Each table must have a column used to identify the owner the row. Any application accessing the row must refer to this column in every query to ensure that one tenant is not able to see another tenant’s data.

Multi-Tenant Database - Shared Schema

Because of the extensive changes required, applications must be carefully tested to ensure that data security is preserved. If an existing application is being ported to a shared schema environment, the use of VIEWs might ease the development effort. Query performance will have to be carefully examined, and additional INDEXes may be required.

While application development will certainly be more challenging, there are many benefits to a shared schema model:

• Development time – As discussed above, all application queries will have to be carefully coded to refer to columns in every table that identify the tenant owner of every row.
• Hardware cost – This architecture allows for high utilization of machine resources. In general, this will provide the cheapest option for implementing a multi-tenant environment.
• Application and database performance – The performance of one tenant may be impacted by the activities of other tenants. Query performance will have to be carefully examined to ensure the proper indexes exist.
• Security – The application must use specially coded queries to select or restrict the data based on the tenant. Robust testing must be used to ensure that a user is not able to see data from other tenants. Unique data encryption for each tenant is not possible.
• Customization requirements – All tenants share the schema, so it is much more difficult to allow for customization. There are a variety of approaches that can be used to provide customization. One approach is to enable a number of generic columns in each table that can be used in different ways by each tenant. Another approach is to make all tables generic, and allow each tenant to describe the complete schema. Application development can become much more challenging using this method.
• The number of tenants – This model is able to handle many more tenants than the previous models. Migration of tenants that require improved performance or capacity may be challenging, as data will have to be extracted from each table in separate operations. Also the ability to restore data for a tenant may be more challenging.

Each table must refer to the TenantID.

As mentioned above, the use of VIEWS may ease application development. In this example code, designed for SQL Anywhere, a VIEW is created to allow access to the Employees table. First a mapping table is created that maps a USER to a specific TENANT.

1
2
3
4
5

CREATE TABLE "TABLEOWNER".UserTenantMap (
    TenantID        				INTEGER NOT NULL,
    TenantUser      				CHAR(255) NOT NULL, 
    CONSTRAINT "UserTenantMapKey" PRIMARY KEY ("TenantID", "TenantUser" )
);

Next, the base table is defined with a TenantID column, and a VIEW is defined, joining with the UserTenantMap table:

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

CREATE TABLE "TABLEOWNER"."BaseDepartments" (
    "TenantID"                       INTEGER NOT NULL 
                                     DEFAULT ( CAST( '1' AS INTEGER ) ),
    "DepartmentID"                   INTEGER NOT NULL,
    "DepartmentName"                 CHAR(40) NOT NULL,
    "DepartmentHeadID"               INTEGER NULL,
    CONSTRAINT "DepartmentsKey" PRIMARY KEY ("TenantID","DepartmentID") 
);
 
CREATE VIEW "TENANTVIEW"."Departments" (
    "DepartmentID",       
    "DepartmentName",    
    "DepartmentHeadID" 
) AS SELECT
    "DepartmentID",      
    "DepartmentName",    
    "DepartmentHeadID" 
FROM "TABLEOWNER"."BaseDepartments" 
	JOIN "TABLEOWNER"."UserTenantMap"
	ON "BaseDepartments"."TenantID" = "UserTenantMap"."TenantID"
WHERE 
	"UserTenantMap"."TenantUser" = CURRENT USER;

The application can be coded to simply SELECT from the Departments table as before. Inserts or Deletes can be handled using an INSTEAD OF trigger defined on the view. INSTEAD OF triggers allow alternate actions to be performed, rather than the DML that caused the trigger to fire. Read more about SQL Anywhere’s implementation of INSTEAD OF triggers here. Here is an INSTEAD OF trigger to handle inserts on the Departments VIEW:

28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

CREATE OR REPLACE PROCEDURE "tableowner"."InsertDepartmentProc"
	(IN new_DepartmentID 	INTEGER,
	 IN new_DepartmentName  CHAR(40),
	 IN new_DepartmentHeadID INTEGER,
	 IN insertingUser		CHAR(255)
	)
	BEGIN
		DECLARE new_TenantID		INTEGER;
		SELECT TenantID INTO new_TenantID
			FROM "TableOwner"."UserTenantMap"
			WHERE TenantUser = insertingUser;
		INSERT INTO "TableOwner"."BaseDepartments" (
			TenantID,
			DepartmentID,
			DepartmentName,
			DepartmentHeadID )
			VALUES (
				new_TenantID,
				new_DepartmentID,
				new_DepartmentName,
				new_DepartmentHeadID
			);
 
	END
;
 
GRANT EXECUTE ON "TableOwner"."InsertDepartmentProc" TO "TENANTVIEW";
 
CREATE OR REPLACE TRIGGER Insert_Departments
	INSTEAD OF INSERT ON "TenantView"."Departments"
	REFERENCING NEW AS new_row
	FOR EACH ROW
	BEGIN
		CALL "TableOwner"."InsertDepartmentProc" (
			new_row.DepartmentID,
			new_row.DepartmentName,
			new_row.DepartmentHeadID,
			CURRENT USER
		);
	END
;

This model can be demonstrated using SQL Anywhere. Similar to the previous multi-tenant demonstrations, there is a PDF that will guide you through the demonstration. This demonstration uses the database in this zip file as a starting point.

(1, 2, 3, 4, 5 )