Advice: Should i use multiple physical databases or one physical database and several logical ones

Question

We are creating a web application that will be used by companies as their business application every day, each company will have many employees and a lot of data.

Is it wise for each company to have its own database or is there a better solution for all companies to be in one database?

Now we use one database and each company has its own id and the employee has a company id. But the database is too loaded with data and the search is slow as well as difficult privacy management.

I would like quick data search in the database, easy implementation and not excessive maintenance costs.

Answer 1

I wrote an article on my blog about this topic based on this question: What is most efficient design pattern for a sql database containing billions of rows per user in a single table?

The bottom line is this:

• If your customers are customers, if they have an office and a phone number, if they are companies --> go single tenant
• If your customers are users, if they come and go, if they can create their own login form a page with their e-mail --> go multi tenant

Answer 2

The answer to your question depends on the business model you want for your clients.

Multitenant Database can be a real headache if some databases are heavily populated

See my old posts

• Feb 11, 2019 : Managing 100s of similar databases
• Nov 15, 2011 : One Big Database vs. Several Smaller Ones
• See all posts in the DBA StackExchange Posts on MySQL Multitenant DBs

Getting back to your Question ...

If you have a high-end client you probably would want a dedicated VM for that client. You could probably charge that high-end client more $$$ for having a private database that could be isolated from application and hardware maintenances if other clients need servicing.

If you have many clients with very lightweight data, those clients could have their databases hosted in multitenant fashions.

Here is another issue that could arise : If their is a common schema change all clients must have, then high-end clients would have their own schedule for maintenances independent of their multitenant counterparts. Perhaps adding indexes to tables could be done for clients with lots of data whereas you could hold off adding indexes for smaller client databases.

What is interesting is this : If a query performs well for one client and not for another, then you have to decide if a query can be improved for the one client on a dedicated VM or for all clients (dedicated or not). This can make query performance come at a premium (on your time and/or money).

To answer your question

• Have a new client with a very large database start on a dedicated VM
• Have a new client with a small database automatically generated in a multitenant environment
• As a client in the multitenant environment get larger, you can migrate it to a dedicate VM and introduce a different pricing model

Make sure the pricing model is properly supported

• Cheaper pricing model for subscribers to the multitenant environment
• More expensive model for dedicate subscribers (of course, factoring in the price of a separate VM per client)