Multi Tenants Database Architecture

Introduction

It is used to address the problem of SAAS which can serve multiple clients. Multi-Tenants database architecture is very useful when one instance of database is serving multiple clients. Only one set of hardware resources is needed to fulfill the requirements of all users. Multi-tenant is based on subscriber model, so user has the freedom to avail the facility as per business requirement or can turnoff.

There are different approaches to the advantage out of the multi-tenants database. These are:

• Dedicated database: Separate databases per tenant
• Dedicated table and different schema: Shared database and separate schema
• Share table/schema: Same database and same table

Now, it is very important to select the appropriate approach for your application depending upon the following factors:

• Size of tenant database
• Number of tenant
• Number of users per tenant
• Growth rate of tenant
• Growth rate of tenant database
• Security
• Cost

1 . Dedicated Database

It is a straight forward approach where each tenant has its own database. Each tenant has its own set of data that remains logically isolated from data that belongs to all other tenants.

Pros

• More secure data
• Easy to customize for vendor specific needs
• Easy to maintain, e.g., backups, restore, etc…

Cons

• Relatively high hardware and maintenance requirements
• This approach tends to lead to higher costs for maintaining equipment and backing up tenant data

2. Dedicated Table and Different Schema

Serving multiple tenants under same database, where each tenant has its own sets of tables grouped with schema as required by tenant.

Pros

• Good for small database application where number of tables per tenant is small
• Cost is low as compared to dedicated database approach
• Moderate logical isolation level is there for vendors having security as a concern

Cons

• Tenant data is harder to restore in case of failure
• Difficult to manage large database application

3. Shared Table/Schema

This approach involves using the same database and the same set of tables to host multiple tenants’ data. A given table can include records from multiple tenants stored in any order; a Tenant ID column associates every record with the appropriate tenant. 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.

TenantID	Col1	Col2	Col3	Col4
1	Abc	..	..	..
21	Cdw	..	..	..
..	..	..	..
..	..	..	..	..

Pros

• Lowest hardware cost as compared to other approaches
• Can serve more tenants per server
• Ability to update the schema in one place and affect all tenants

Cons

• More security is required to make sure no one can access cross-tenant data
• Can affect query performance because of more rows
• Can only update the schema in one place and thereby affect all tenants

Extension Table

In case there is a need to increase the number of fields as per tenant requirement under approach 3, then?

As all tenants will share the same table/schema, it is very difficult to customize the number of fields.

One way to avoid these limitations is to allow tenants to extend the data model arbitrarily, storing custom data in a separate table and using metadata to define labels and data types for each tenant’s custom fields.

TenantID FN Field TagID 101 Kim Trade 221 202 Tim HR 433 ……………. 342 Rim Fin 510 (Data Table)	TenantID ExtID Label DataType 342 3990 Age int 120 3122 Status bool ………………….. 202 1200 LName string (Metadata Table)
TagID ExtID Value 433 1200 Border 500 321 abc …………… 510 3990 23 (Extension Table)

Here, a metadata table stores important information about every custom field defined by tenant, including the field’s name (label) and data type. These fields are created dynamically on front end (GUI) with unique id and value entered by end user corresponds to these fields are stored in different table Extension table.

So corresponding to data table, we need to create two new tables “MetaData” and “Extension”.

This approach allows each tenant to create as many custom fields as necessary to meet its business need. When the end user retrieves a customer record, it performs a lookup in the extension table, selects all rows corresponding to the record ID, and returns a value for each custom field used. To associate these values with the correct custom fields and cast them to the correct data types, the application looks up the custom field information in metadata using the extension IDs associated with each value from the extension table.

This approach adds a level of complexity for database functions, such as indexing, querying, and updating records.

Reference

1. http://msdn.microsoft.com/en-us/library/aa479086.aspx#mlttntda_topic1

CodeProject