Build a SaaS application, part 1/3 : the principle of storing data

 Nowadays Software as a service platform are the most used models to build web applications in most startups out there. So as a web developer it can be really good to know even the basics of this. So in this small article I am going to share with you the principle of this.

The general behavior or such kind of platform is to enable someone to create their account and have a whole space in which he can use the software, some platform, even provides a sub-domain for his users, for example wordpress.com blog hosting platform or slack.com messaging software. So I am about to respond to this question in this article: 

How data are actually stored in the database ?

So we will start with the principle of storing data: How data are actually saved in the database ?

In the world of SaaS applications, each instance of the application is called a tenant, so the goal here is to find an efficient way to store and retrieve each tenant's data into the database without losing data integrity. Generally there are 3 ways to store tenant's data in the database, let call them data isolation level, they are: High data isolation, mid data isolation, low data isolation. Each of them includes a very specific process to manipulate information in the database, let dive into them.

1. Low data isolation level

The principle here is to store data into the same database within the same tables "side by side". This implied that in all tables you will have a column (named for example tenant_id) which is related (foreign key) to a central table named tenants for example, it will contain the list of your tenant (user's instance of the application), like this when manipulating data you will just have to a statement which will specify which line are concerned by the operation. In SQL it gives something like:

SELECT .... FROM table_name WHERE TENANT_ID = 'TENANT_ID'

Even as all tenant's data are saved into the same table, you can specify which data should be actually manipulate just by specifying the tenant_id. Knowing what tenant_id to use will be discussed in the next part of the article.

2. Mid data isolation level

Actually, this level of isolation involves the use of an advanced concept in database system like PostgreSQL (I really love this one): the schemas. What is a database schema? according to wikipedia it is:

A pictorial representation of the relationship between the database tables in the database that is created.

This is somehow weird right? ok; Just simply remember that a schema is a database into the database. The fact here is that you can replicate a set table with their relationship within the same databases, but in different schemas so it will look like there is many instances of the same database. For those who are familiar with PostgreSQL they use to see a public schema in which their tables are created.

Talking about our tenants, each of them will be represented by a whole new and independant schema.

3. High data isolation level

The principle here is just to store each tenant's data in a different database. Yeah a whole new database, not a schema. This is the more resources consuming method because it evolves to run a CREATE DATABASE kind of query for each client, but the best way here is the fact that a tenant's data will never suffer of stuff like table's lock or problems created by multiples and repeated access to the same database. This method should be used for very and very large application and for those who have a lot of hardware ;).

We reach the end of this, i hope you enjoyed reading this, if you want more technical detail on this just check out this on medium. The next article will be about how to how to relate a user who use the application (via the web browser of course) to a tenant: let me introduce you the Tenant detection strategy.

Regards. Adonis