Multi-tenant database versus multi-database design for web application

Question

Assuming that I have some sort of enterprise accounting software system(web application).

I have many individual companies/customers that use this system.

So each company when they use the system. They use it to perform those financial related transactions. E.g. keep track of who they make payment to, keep track who are their creditors and debtors etc.

Design #1:

I as the owner of this software system, is the 'super admin' of this entire web application.

The web application is designed in such a way that I as the super admin can use the user interface on the web app to adjust the settings and configuration for each of my customer using my system.

An example of the page I see when I login as the super admin is below:

I as the super admin can go in and adjust the configuration for each company.

I as the super admin can also access another kind of control panel which I adjust some settings which applies to multiple or all my customer at once.

The system is designed in such a way that each company can have their own 'administrator' who can modify the settings that affect only their own company and their own users.

The database design is generally a 'monolithic structure'. 1 database with more than 50 plus tables which of course caters to roles and privileges of the different users in the entire web application.

I can appreciate the certain control the super admin has over all the companies. There is some level of convenience as he/she can maniupate the entire web app based on whatever the customers tell him/her.

Design #2

My question is what happens if I design a similar kind of accounting software system. But the way I do it is, I create a database of each company and there is no super admin user interface for me to maniuplate my customer's system.

If I have 10 customers. I have 10 database, then each database will have a less complicated database design.

Then I use version control to control the source code and database design for my 10 unique customers.

---

I am curious what is general pros and cons of this 2 designs.

Is there a difference on how the web application can be 'distributed' across multiple application server/database server for these 2 different design?

I ask this question because I have only a limited understanding of how a software system can be 'divided across multiple servers' to make sure the system performance is good.

Thank you.

Answer 1

If I have 10 customers. I have 10 database, then each database will have a less complicated database design

Only slightly less complicated, though.

Your scenario 1 is what we call a "Multi-Tenant" database. The only real design difference between that and multiple databases is that, in a multi-tenant database, you have a TenantID field in every table. Every query in the database must be filtered by the currently logged in Tenant.

Multi-Tenant databases are much easier to maintain if you want to roll out new software features to all of your tenants. You can also run queries across all tenants (say, for statistical or billing purposes) much easier. The catch is that there is no physical separation of data, so leakage from one tenant to another can occur if a bug creeps into your program.

Answer 2

As Robert Harvey has noted, while the database is somewhat simpler, have multiple DBs adds some complexities of it's own. Aside from pushing changes to all the DBs, there's just the management and maintenance of all those instances: provisioning, backups, maintenance, etc. Instead of managing a DB, you are now managing a farm of DBs.

This is by no means impossible but it's a different problem scope. Before you go down that path, you should invest some time and potentially money into automation. If you try to manage a lot of DBs the way you manage one instance, it's going to be a struggle in terms of time alone. It's also far more likely you will make a mistake when you are executing the same task 10 (or more) times across all these instances.

In terms of 'dividing across multiple servers', that's not necessarily a requirement. You could use virtual machines (VMs) or containers to run these instances on a single physical machine or a number of machines smaller than the number of instances. Or, if you are using a managed service from a cloud provider, you don't need to know about the servers at all. From a logical application perspective, there's no real difference between theses approaches as long as there's a distinct separation between them.

Whether you want to go this direction depends on a lot of factors. The big ones that come to mind are:

1. Security - AS Robert Harvey mentions, it's easy, maybe likely, that you could expose one customer's data to another customer. Is this a big concern? It could be: if those customers are competitors, for example. If these are public companies, non-public data could be leaked and create compliance issues. Some customers might just refuse to allow you to host there data in multi-tenant database and if you want their business, you have to have a separate DB. If that's a likely scenario, I think you are better off addressing it holistically.
2. If you are dealing with large volumes of data, a multi-tenant DB may become unwieldy. Maintaining a good level of query performance may become difficult.

With regard to 2, one solution is database sharding which could be based on tenant ids pretty easily. But this starts to look a lot like having separate DB instances and shares a lot of the same challenges.

I'd probably stick with a multi-tenant solution unless you have some factor or factors that are pushing you towards separate instances.

Answer 3

Robert covered some of it. On the other hand:

Multi-tenant databases are easier to maintain, in that you only update your scripts in one place. However, as long as good development practices are maintained, it's not a far stretch at all to say "here's a script file to run for updates, run this against each separate database." Quite simple in the broad scheme of things.

I think the potential data leakage he mentions as well as a huge factor. Imagine two competitors both using your software, and you introduce a bug that gives one of them access to financial records of the other. As developers we always run the risk of bugs that adversely effect our clients... risk management should certainly be considered, and this is a huge risk. Is it worth it, for the slight simplification of having all data in one db with another foreign key tossed in? (granted this is hardly impossible with each client on their own stack, but can be quite a bit more difficult to do accidentally)

Next up, scaling. What happens when your client base becomes too big for one server? Going from a multi-tenant db to single-tenant db after the fact isn't always a huge job, but when weighed in with everything else...

Or, what happens when one client is far bigger than the others and taking up lots of SQL resources running reports? That one client could monopolize resources and all the others in your tenant database can suffer (we try to avoid these types of scenarios within a single enterprise system by splitting out reporting databases from live transactionals (OLAP vs OLTP, etc)).

I've done both ways before. I did one database with multiple tenants the first time, and every similar project since has been separate dbs, web hosts instead. I'll not go back to muilti-tenancy, IMO the long term risks and debt just don't pan out when looking at the (very) slight inconvenience of having to manage 10 databases instead of one.