Skip to content

Databases

How to Architect a Multi-tenant Database?

What is a Multi-tenant Database?

SaaS is in fashion now and developers working on SaaS applications and related skill sets are much in demand. You may be aware that one of the five essential attributes of a SaaS is multi-tenancy. Your SaaS must be multi-tenant to reduce maintenance and support cost and in turn make it cost efficient. Simply stated, from software perspective tenants mean clients, hence a multi-tenant architecture is the one which has the ability to support 100s of clients by using only one database and code base! It means that unlike the traditional model you don’t need to develop one custom code base and one custom database for every client to deliver your application.

Here you’ll learn about a few basics of architecting a multi-tenant database. Designing the code base is out of the scope of this article.

For Architecting a Database You can Opt for any One of the Following Three Approaches:

1. Separate Database

2. Shared Database Separate Schema

3. Shared Database Shared Schema

It fully depends on your requirement as which approach to choose but if you want a truly multi-tenant application I would suggest the 3rd approach i.e. Shared Database Shared Schema.

What is Shared Database Shared Schema?

In this approach you’ll store all the records of all your clients in the same set of tables in a single database. In this approach as there is no hardware separation the records (data) are logically separated in the database. So, this approach requires a lot of smart planning and significant development effort so that the logical separation of data works out without compromising the security and integrity of the data.

Initially a multi-tenant database requires significant time and effort (hence a bit expensive) but in the long run it pays off multiple times by reducing the maintenance, support and server (in terms of hosting charges) cost.

How to Logically Separate the Database?

To logically separate the database you can model the most generic tables on the following lines:

1. Create a Client (or Tenant) table.

    • Auto-generated ClientId (or TenantId) could be the primary key.
    • Store client specific details like name and address.

2. Create Role table

    • Columns: RoleName and RoleId

3. Create a User table.

  • One client / tenant can have many users and each of them could be mapped to a specific role.
  • User role columns could be UserId (Primary key), ClientId (foreign key from Client table), RoleId (foreign key from Role table)
  • Note: This User table should not be confused with the end user or the actual consumer of your client’s services in case your clients are offering B2C service (and yours is a B2B service).

While writing this as I don’t have access to any database, I am sharing you a rough sketch of the tables, just to give you an idea [varchar = string]:

Multi-tenant Database

 

While fetching the data you can identify the records based on UserId and ClientId condition as per your requirement. In most of the cases if you want your database to strictly follow the good practices of database modeling and Normal Forms you will not require ClientId in any of your other tables. The UserId will be more than enough in every other table to track the transactions related to any client as you can always fetch the name of the client by using an inner join on User and Client tables.

In a few cases where you require best of performance from your SQL and hence want to minimize number of joins you can deliberately introduce a few degree of redundancy by adding ClientId in a few tables.

How to Cater to the Needs of Different Clients?

Multi-tenant SaaS applications are best fit for certain applications like CRM where most of the requirement has been standardized during past years or so but in its current avatar, SaaS or the Database modeling is certainly not matured enough to take care of say a complex system like an ERP solution. This is because organizations using ERPs have many different requirement (workflow) based on various scenarios which are difficult to model in a single database that can fulfill the needs of every client.

Even CRM or Human Resource management systems which are by and large considered as standard may require a certain degree of customization, not by code changes but via configuration (settings) by your clients. No matter how much care you’ve taken to design a database you’ll certainly find a client who wants to add one or two extra fields in few of your data entry forms/screens.

Suppose, you’ve designed a form and database table that allows your user to enter Twitter and LinkedIn Id but once you release the application a few clients may ask you to provide a few more social media links like Facebook Page, G+ or Pinterest link or something which is expected in future! You can’t simply ignore their request.

So, you must architect your database that is flexible enough to accommodate these requirements. This can be achieved using various ways like using a Metadata and Extension table combo. In this approach your clients can add fields i.e. label along with its data type (string, integer, date etc.) in the data entry screen.  Metadata table will store the label and data type information with ClientId and ExtensionId. Now when a client enters a record against these entries it can be saved in the Extension table along with ExtensionId as a foreign key from the Metadata table. The primary data table will have a foreign key reference to Extension table.

The above model offers the much needed flexibility to your clients to extend your data model and add as many arbitrary fields as they may require.

Bottom-line

The above discussion may excite you to start modeling every database as a multi-tenant architecture but before jumping to any conclusion please understand the requirement carefully. A few applications that deal with confidential information like patient information system in hospitals, pay roll processing, government quotations/contracts etc. might not be a fit case for multi-tenant database. What do you think? Please share your views.

Be Sociable, Share!
    The following two tabs change content below.

    Basant Singh

    Basant is a talented software developer who is very engaged in web & cloud platforms on a daily basis. He is a contributor to codingthis.com reporting on various news and troubleshooting on various platforms. Basant is very strong in SQL and a great asset to the CodingThis community.

    Latest posts by Basant Singh (see all)