- SAP Managed Tags:
This set of articles was first published on my Sybase blog in 2009. Since that time, this 5 part series has been one of the most requested set of articles. As part of Sybase's integration with SAP, I am republishing them on the SAP SCN, and taking the opportunity to update them.
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.
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.
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:
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:
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
;
I encourage anyone interested in the Multi-Tenant database topics I have discussed in this series of articles to check out the SQL Anywhere on-demand edition.
