Click here to Skip to main content
13,148,427 members (64,217 online)
Click here to Skip to main content
Add your own
alternative version

Stats

28.2K views
6 bookmarked
Posted 13 Jan 2010

Multi Tenants Database Architecture

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

TenantIDCol1Col2Col3Col4
1Abc......
21Cdw......
........ 
..........

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.

TenantIDFNFieldTagID
101KimTrade221
202TimHR 433
…………….
342RimFin510

(Data Table)

TenantIDExtIDLabelDataType
3423990Ageint
1203122Statusbool
…………………..
2021200LNamestring

(Metadata Table)

TagID ExtIDValue
4331200Border
500321abc
……………
510399023

(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 MetaDataand 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)

Share

About the Author

puri keemti
India India
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170924.2 | Last Updated 13 Jan 2010
Article Copyright 2010 by puri keemti
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid