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

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

CodeProject

Multi Tenants Database Architecture