Potential Problem and Solution
Let's imagine that we are a SaaS solution provider. Your customers are primarily companies that want to have their own space in the scope of your service where their users can work together around your service. In this article, we will refer to your clients as tenants. The solution uses a relational database. During architecture design, when you already know that you will pick a relational database, you may face of choice between a single database or multi-tenant architecture.
Suppose there is a requirement that the application should allow sharing of data (CRUD) between tenants, and sharing by calling REST API or some queue solution is not acceptable from a performance standpoint. In that case, a single database solution seems to be the most reasonable in which you just need to execute SQL operations. Although with some compromise, you might achieve the same effect with multi-tenancy architecture that uses a shared schema strategy by defining some of the tables that should not be isolated by the discriminator column (described in the latter part of the article) and applying additional queries that are used for authorization. However, we must remember that isolating tenant data in the single database solution requires a lot of additional code that the developer has to implement to protect data. This approach might complicate implementation without mentioning sharing data, making developers work much harder. In case when sharing data between tenants is not our requirement. A single database solution can still be our choice, but the additional code we would have to implement increases the time and effort to deliver it. By choosing multi-tenancy architecture, we might get rid of this problem. Of course, choosing this approach comes to some other challenges that we would not have with a single database, and they are different depending on the picked strategy, but this topic is for another article. To go ahead with multi-tenancy architecture, we must decide which strategy to pick. The most popular three strategies are separate database, separate-schema, and shared-schema. And also, in this case, each strategy has pros and cons. From an isolation standpoint, the strategy of a separate database guarantees the highest level. And shared schema strategy has the lowest level compared to mention two others. That is why it is important to ensure your potential customer agrees that his data will be stored in the same schema. As for performance, the separate database seems to be the best choice. Although the application would have managed a database connections pool for each tenant, operations on a single database would be executed only for one tenant. But also, please remember that by implementing the separate schema or shared schema strategy, we can combine it with a separate database. For example, by keeping one group of tenants in a single database with a separate/shared schema and one tenant, we have guaranteed the highest performance and data isolation level in a separate database. Such an approach would also require some wise routing strategy, but this topic is for another article. The most significant advantage of the shared schema strategy is that it requires the least amount of resources, reducing costs. It would help if you considered other things when choosing the right strategy, like DDL operations execution, fault tolerance, and many others. But it is not the topic for this article.