Posted by: keemtipuri | January 8, 2010

Multi Tenants Database Architecture

Multi Tenants Database Architecture

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 to 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 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 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 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 incase 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

Incase, there is need to increase number of fields as per tenant requirement under approach 3 then?

As all tenants will share 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: