Organizing a database for several companies?

Question

MySQL

The idea is this:
There is a site site.com 1.
The user registers a company 1.1
a subdomain is created with the name of the company
1.2 a database is created (question below) database) Questions: 1. How to organize a database? a. I do not want to create a lot of databases, despite the fact that it will be difficult to support (migrations) b. For all companies to store everything in one table? - it seems to me not safe, but the easiest way is with. For each company, create new tables in the current database, prefixed with the company name. - the same problem as in option a.
2. Maybe there are some more adequate and concise solutions to this problem?
The project will be written in YII2

Answer 1

In fact, with the proper development process, having one table for all campaigns or several should not cause a headache, because. on a good note, all changes in tables (their structure) during release should occur with the help of migrations, i.e. nothing should prevent you from running all migrations for each of the campaigns in order to update the structure of all tables.
If you are making a "global" product, I would choose the option if I were you: many tables with many campaigns, when each table can store a data set for several campaigns, but there can be many such tables. This will give load distribution (different tables can be stored, processed on different servers), so. you can always easily allocate new resources for a new campaign.
On the other hand, you can store all data in one DB (one table). At the initial stage, it will be easier for you, but the longer you develop the product, the more difficult it will be to switch to another architecture. Therefore, it is better to think about everything in advance. At least see the pitfalls and prepare to tackle them in the future.

Answer 2

if according to Feng Shui, then option a. the rest is from the evil one.

Answer 3

The architecture is not entirely clear. Are you writing something like CRM? That is, by creating a company, the user gets access to certain tables (for example: customers, payments, products, tasks, notes). Is the database structure for each company the same?
Then you should look for how they do it in popular CRM systems, I think there is an option with storing data from all companies in common tables with company_id fields, there should be no problems with integrity.

Answer 4

You can make prefixes for naming tables, if the data is stored in different tables, but in the same database. Although option A, as mentioned above, would be the most appropriate here.