Click here to Skip to main content
Click here to Skip to main content

Multi Tenants Database Architecture

, 13 Jan 2010
Rate this:
Please Sign up or sign in to vote.
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

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

puri keemti

India India
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140709.1 | Last Updated 13 Jan 2010
Article Copyright 2010 by puri keemti
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid