Database design in SaaS platforms

header

We introduce this next article from the series of designing SaaS platforms, where we describe common patterns of multitenant architectures. We’ll show you 3 approaches of the database modeling with scalability, security and maintainability in mind.

Single Database – all tenants in one database

Single Database

Single Database

In a Single Database Model, we keep all tenants’ data in a single database. Entities are marked with some Tenant ID attribute which is just a foreign key to the Tenants table.

Pros:

  • Ease of implementation – on an application side there aren’t needed any difficult to implement databases switch mechanisms. We’re using single database connection for a HTTP request.
  • Fast deployment – adding new tenant is as simple as inserting the new Tenant entity to the database. We don’t have to make any schema changes for new tenants nor creating new databases.

Cons:

  • Security – access for resources must be strongly controlled on the application side to prevent unauthorized access between tenants and mixing different tenants’ data. To achieve that, some type of an Access Control List mechanism must be implemented.
  • Scalability – lack of partitioning can lead to scalability problems – under growth in number of customers, switching to a sharded models (later described in the article) may be required to ensure good performance and stability of the data storage.

Use case

In one of our SaaS platforms, we’ve used this approach due to expected relatively small number of records added by each of the customers. We’ve also needed structure which provides fast deployment of new tenants to let for fast creating new accounts just from a landing page.

Isolated Databases – single database per tenant

Isolated Databases Model

Isolated Databases Model

This approach assumes creating dedicated database instances for each customer. It basically means sharding the database by tenants.

We have to keep some type of a centralized tenants’ metadata dictionary with information that allows connections to the tenant’s database.

The simplest way is to have a master database with a tenants table where we keep associations of tenants and their database connection data

tenant1 -> Tenant1 db connection string; auth data
tenant2 -> Tenant2 db connection string; auth data

The first thing after the HTTP request arrives, we retrieve ID/name of the tenant (e.g. based on the host name). Than we try to find him in a master database and, using retrieved data, connect to his specific database.

Pros:

  • Scalability – we instantly gain sharding of the database which gives better performance under expected high number of resources added by each tenant.
  • Security – distribution of tenants gives us physical isolation of their data. The risk of mixing customers records in database queries is avoided.
  • Custom schema for tenant – it’s possible to add some custom entity properties dedicated for tenant.
  • Ease of migration old applications to multitenant approach – if we already have application designed in a single-tenant approach, such model will facilitate the migration of such platform to multitenancy.

Cons:

  • Uneven distribution of data – there can be situations when some of tenants will use many more resources than others. It’ll be strongly visible when having account types with different limitations (e.g. trial, basic or enterprise accounts).
  • Increased required resources – creating new database for every tenant will lead to higher usage of server’s resources (memory, disk). Also you should remember about limitations of the technology e.g. maximum number of databases in a MySQL instance.
  • Need of holding second database connection – when storing tenants’ metadata in a master database, we have to make another connection to a tenant’s specific database. It likely won’t be possible to maintain a connections pool for every tenants’ databases.
  • Difficult deployment and maintenance – adding new tenant will lead to creating a new database, which usually isn’t a fast operation. Also maintaining multiple databases is much harder in comparison to a single one – operations of updating schema must be done on every tenants’ database.

Use case

When designing the GoApp platform, we’ve decided to use separate databases for every tenant. The platform is used by local governments and enterprise-grade customers – the highest security level is a must. It’s ensured with that model, which gives physical isolation of databases.

Hybrid Database Model

Hybrid Database Model

Hybrid Database Model

It’s the most complex structure, which is based on the first and second models. It tries to minimize negative sides of previous structures but is the most time consuming to design and develop.

We store tenant id associations in every entity, like in a single database approach, but we also have a master database with tenants’ connections metadata.

With that model we can store some tenants in a single databases and others in a separate ones.

Thanks to that we can, e.g. have databases such as:

  • Trial accounts – database to store all tenants of the trial type, which are planned to have limited number of resources.
  • Basic accounts – 2x databases.
  • Professional accounts – 6x databases – as such type of clients can store 3x more resources than basic ones.
  • Enterprise accounts – dedicated databases for every tenant to keep high stability and high performance (as we guarantee SLA for that type of accounts).

To allow further migration of tenant’s data from one database to another, we must remember to have unique IDs of entities across all databases.

Pros:

  • Scalability – we can dynamically shard database across tenants based on their resources usage and account types. At the beginning we can even have just single database and scale out by adding next ones as the customer-base growth.
  • Security – such structure still allows for keeping separated databases for the most demanding customers.

Cons:

  • Model complexity – data of tenants is mixed in different databases. We’ve to store their connections metadata in a master database.
  • Resources IDs – IDs of resources must be unique across all databases to allow further migrations of data to the other database. We can use centralized service for generating IDs, unique GUIDs or complex keys of {ResourceID, TenantID}.
  • Difficult deployments – just like in an isolated model – under maintenance and upgrading platform we have to keep in mind all of the databases.

Use case

We’ve used that approach in Inspeo Analytics platform. Using Elasticsearch, we’re additionally sharding database by multiple indices for the enterprise accounts, which, by definitione, store a higher number of resources.

Conclusions

Described models shows different approaches of designing data layer of a SaaS applications. When choosing one, you must be aware of its negative sides as each has some.

Keep in mind your planned capacity, types of clients which will use your platform and their demands of security and availability — e.g. your SLA.


Are you looking for experienced team to develop your SaaS platform? Check out Octivi!

Looking to scale-out your
web application?

Hire Octivi!

Antoni is a Software Architect and Scrum Master at Octivi. He is responsible for software architecture of our key projects, he also holds Professional Scrum Master certificate.

  • Srinivas Paruchuri

    Appreciate the article, couldn’t have explained multiple DB formats for SAAS (multi-tenant) in the most simplest language.
    regards,
    Srini Paruchuri

  • Paul

    Great article! Thanks a lot.
    While talking about SaaS, do you know any good online tools for database design?

    • Sean

      MySQL Workbench is my favorite tool for schema planning.

      • Victor

        Workbench is a desktop tool and it’s meant for MySQL only. If you want more universal and a fully web-based tool for database design, try Vertabelo. It suports database design for several databases. I don’t know any other SaaS app for data modeling.

  • Manjunath

    Which databse(mysql/mongodb/oracle) is good for SAAS applications

  • Reynier PĂ©rez Mira

    This is a great article but I would prefer if you can write a small one with a real life use case so we can take a closer look, can you write a new one?