The problem of table partitioning in a multi-tenant CRM system and how to trigger the generation of tables and data.

Our company is currently working on a multi-tenant CRM management system. Since the amount of data is already huge, we have recently begun to consider the issue of sharding databases and tables.
For multi-tenant CRM systems, there are currently three main ways of organizing databases:
1. A single tenant has a single database.
2. A single tenant under a single database has a single schema
3. Multiple tenants share a single database (use tenant IDs to distinguish their data)
Based on various considerations, we currently adopt the second method : In a single database, each tenant has its own set of data tables. The table name of each table is in the form of [table name tenant id].

However, after the database structure was determined, I encountered several problems that I did not understand. That is:
1. How to manage these tables? Use php code to manage or is there such middleware on the mysql side?
2. When should the generation of data tables for newly registered tenants be triggered? (The current trigger position is to directly generate the user table, menu table, etc. when a new user registers. And when he uses other functions, the relevant function tables are generated again. It always feels like this There is a problem with the method. Is there a better way to handle it? )

Is there any technical guru who has worked on multi-tenant CRM systems? Can you give me some advice? Thank you very much

Reply content:

Our company is currently working on a multi-tenant CRM management system. Since the amount of data is already huge, we have recently begun to consider the issue of sharding databases and tables.
For multi-tenant CRM systems, there are currently three main ways of organizing databases:
1. A single tenant has a single database.
2. A single tenant under a single database has a single schema
3. Multiple tenants share a single database (use tenant IDs to distinguish their data)
Based on various considerations, we currently adopt the second method : In a single database, each tenant has its own set of data tables. The table name of each table is in the form of [table name tenant id].

However, after the database structure was determined, I encountered several problems that I did not understand. That is:
1. How to manage these tables? Use php code to manage or is there such middleware on the mysql side?
2. When should the generation of data tables for newly registered tenants be triggered? (The current trigger position is to directly generate the user table, menu table, etc. when a new user registers. And when he uses other functions, the relevant function tables are generated again. It always feels like this There is a problem with the method. Is there a better way to handle it? )

Is there any technical guru who has worked on multi-tenant CRM systems? Can you give me some advice? Thank you very much

If it were me, I would definitely use software to solve the logic of initializing the schema first, so as to achieve the goal the fastest. Later, consider using middleware based on the actual situation.

As for the timing of triggering the initialization action, it depends on the resources and time required for your initialization action. If the consumption is negligible, then the new tenant will be initialized immediately after successful registration; if it is time-consuming and resource-consuming, cron will be run regularly (business needs an interval from registration to use of the tenant, such as registration review).

According to my experience, just creating dozens or even hundreds of tables (MySQL) does not consume much resources ~ it will not be used in the blink of an eye