SaaS: one database per client, or a shared database?

Question

MySQL

Suppose the task is to develop a CRM system and provide access to it in the form of SaaS, which means that there will be many clients, but the data of different clients is not available to others. The client is understood as one account, for example, the account of the company "Horns and Hooves" for keeping records of its clients.
You can see two options for storing data: store everything in a common database and separate the rights by application logic, or store data in an individual database for each client? What are the advantages and disadvantages of the first and second options?
Additional conditions:
As a DBMS, let's take, for example, MySQL, and as a platform, the Amazon cloud.

Answer 1

I solved a similar problem and ran into the same questions as you.
On the one hand, based on the client - unacceptable waste. As a rule, Saas means thousands of client companies, and there will be no number of those who just come to look.
If there is only one database, soon some rapidly growing table will become “unmanageable” and seriously reduce performance. Of course, you can split such tables into partitions, but in our situation there is a more interesting solution.
It is necessary to keep several databases and several hundred companies in each. At the same time, new databases can be added as they develop.
For a while, one base will generally be enough for you.
A few recommendations:
1. We assign one of the databases (obviously the first one) as the master or “system” database. only it will store data common to the entire system. For example, the news of your system, global settings, and of course the main thing - a list of client companies.
2. Add the COMPANY_ID field to all tables with client data. Moreover, make it a part of all primary and foreign keys. That is, the primary key of the ORDER table will be (COMPANY_ID, ID)
3. ID will be incremented within the company, and not the entire table. Those. each company will have an order with ID = 1,
2, etc.
Example:
COMPANY (id, name) - "system" table with client companies
ORDER( company_id, id, customer ) - "client" table of orders
PRODUCT( company_id, id, name ) - company's product catalog
ORDER_ITEM( company_id, order_id, product_id) - products in the order
primary keys:
in ORDER and PRODUCT - (company_id, id)
in ORDER_ITEM - (company_id, order_id, product_id)
foreign keys in ORDER_ITEM:
(company_id, order_id) → ORDER( company_id, id)
(company_id, product_id) → PRODUCT( company_id, id)
This approach gives, firstly, the maximum isolation of company data.
Secondly, the possibility of moving a company from one database to another without a conflict of primary keys.
If you have any questions - write to the local mail;)
Good luck.

Answer 2

Postgresql has one more level besides the tabular one - scheme. By default, all tables are created in the public schema; different schemas can have tables with the same name.
As a result, it would be possible to create schemas for different users in one database.
Perhaps mysql has a similar functionality?

Answer 3

The variant with a DB under each client - looks perverted. And the possibility of organizing fields in forms by the client can also be initially laid in the database structure.

Answer 4

I strongly advise you to use postgresql better for this task. Well, it just works better than mysql.
Each of your approaches has its pros and cons.
If there is one database:
+ It is
convenient to operate with one database It is
convenient to roll update scripts onto one database It is
convenient to administer one database
- the database grows strongly if there are many clients (a significant disadvantage)
Tables grow, performance decreases
If there are many databases
+
the database does not grow much
Tables do not grow
- Not convenient operate with several bases base
It is not convenient to roll update scripts over many bases
It is not convenient to administer many databases, administrative interfaces become more complicated
I would go the following way:
If there is a lot of data on average from a client, then there is a separate database for each client.
If there is not a lot of data on average from a client, but there are a lot of clients, then I would do it in one database.