Structuring a Saas Database

Question

Well, I’m studying a little bit about applications .NET as Service and not as a Product, so that a monthly subscription customer’s use of the application.

As long as the database will be unique and the hosting is all my responsibility, this would be the correct way to structure the database?

Answer 1

This is an issue that generates a lot of discussion, since many factors should be evaluated. My intention with this answer is not to tell you "do this and this way" but to indicate possible ways for you to deepen and evaluate what best suits you.

A while ago I made a similar question and I got a very interesting answer that helped me guide me on a project that I was doing. Based on this question and the answer contained in it, I studied several concepts that led me to make architectural decisions for the project in question, below a summary of what I understood of the answer and some more information that may be useful to you gives a look at the original question to enrich the debate.

Basically, one must assemble a strategy to attack two main points, the data architecture and the application architecture.

Data architecture

The data architecture is paramount, wrong choices at this point can yield a considerable amount of extra work when the application is in production. And when we are thinking about a Saas solution it is paramount to have data isolation between customers, between users and between environments (beta, production).

There are two strategies basically:

1. Individual databases per customer: In this approach, each client has its own database, thus ensuring the isolation of information. Although it is the most logical way forward, we must assess the issue of the cost of maintaining the structure in case of migrations.

2. A database for shared to all customers: In this approach we have only one database that contains the information of all customers and the separation of the data takes place through strategies of segregation of the information per client and per user. Legal, in this case we keep only one database instance and when it is necessary to perform some maintenance in the bank the maintenance cost will be much cheaper, since we will do the process only once. On the other hand, our database and processing cost will be much higher and we may face memory limitation problems, Iops (in the case of cloud banks).

For the two approaches you must prepare your application/API to work in the desired way and for this there are numerous strategies that do not come to the case show here.

Application architecture

Despite not being the focus of the question, the application architecture is extremely important to be cited and is a determining factor when we will work with multitenancy.

The application architecture should allow it to be resilient and scalable first of all. It is no use having a well-defined strategy to work with the database if our application does not have these two characteristics.

Think about me, if we have only one instance of our application running and receiving traffic from all clients, one time or another the server will open its beak, then you already saw right... it screws up everything, causing unavailability and locking the application.

That’s why it’s important to think about the architecture of the application, in my experience with the above application I faced several problems.

I created the well-structured application to make it possible to work with either of the two database strategies, plus my API was a big monolith and when something consumed considerable processing it matched the other users by increasing the response time and throttling my application, not to mention the cloud processing costs that went up there at peak times.

Today we have several techniques and concepts that can help us create resilient and scalable applications:

• Microservices to ensure isolation and prevent a specific problem from impacting all customers;
• CQRS architecture for separation between Read and Write strategies;
• Cache strategies to reduce database access;
• We can use the concept of messaging using a queue to store the data before data persistence;
• We can use the Gateway Api and API Management concept to manage microservices efficiently.

As said at the beginning, it is a broad subject that can generate a lot discussion, the key is to study and see what best applies to your need. Below I will pass on some legal links that may help to study better solutions:

Desmitificando multitenancy series

What is multitenancy

Wikipedia (of course, could not miss)

Mensageria is not a bug of seven heads

Introduction to the monthly payment

CQRS what is? and where to apply?

API Gateway governing microservice architecture

I hope I’ve helped you define something more concrete.