Design of crm system statistical table

Currently, we are working on a crm statistical function. we need to create tables to store various user-related data. For example, count the number of new customers of an employee on the current day. A statistical table is designed to store the daily customer count of employees. Then, when an employee adds a customer, add 1 to the corresponding field in the statistical table. Delete...

Currently, we are working on a crm statistical function. we need to create tables to store various user-related data.
For example, count the number of new customers of an employee on the current day.
A statistical table is designed to store the daily customer count of employees.
Then, when an employee adds a customer, add 1 to the corresponding field in the statistical table. When deleting a customer, the corresponding field in the statistical table is reduced by 1.
This triggers the operation of adding 1 minus 1 and uses swoole for asynchronous execution.

Recently, you also need to add statistics for this week, this month, this quarter, and this year.

I copied the [Daily statistical table] in four copies, corresponding to the [Weekly Statistical table], [monthly statistical table], [quarterly statistical table], and [annual statistical table ].
The data structures of these five tables are similar.

My current problem is:
1. is it optimal for me to use asynchronous triggering to count the number of daily customers? Is there any better way?
2. weekly, monthly, quarterly, and yearly data are calculated from the [Daily statistical table, is it better to directly create these four tables or use asynchronous triggering to directly store relevant data?

Is there any expert to answer this question? thank you very much.

Reply content:

Currently, we are working on a crm statistical function. we need to create tables to store various user-related data.
For example, count the number of new customers of an employee on the current day.
A statistical table is designed to store the daily customer count of employees.
Then, when an employee adds a customer, add 1 to the corresponding field in the statistical table. When deleting a customer, the corresponding field in the statistical table is reduced by 1.
This triggers the operation of adding 1 minus 1 and uses swoole for asynchronous execution.

Recently, you also need to add statistics for this week, this month, this quarter, and this year.

I copied the [Daily statistical table] in four copies, corresponding to the [Weekly Statistical table], [monthly statistical table], [quarterly statistical table], and [annual statistical table ].
The data structures of these five tables are similar.

My current problem is:
1. is it optimal for me to use asynchronous triggering to count the number of daily customers? Is there any better way?
2. weekly, monthly, quarterly, and yearly data are calculated from the [Daily statistical table, is it better to directly create these four tables or use asynchronous triggering to directly store relevant data?

Is there any expert to answer this question? thank you very much.

What are your customers? For example, you can store a table for millions or lower. The table has two fields:created_at,deleted_atAnd are of the datetime type, and then the two fields are indexed.

You can query data in real time in the future, and the speed will be fast at or below the million level, and you can check the scope if you want. Adding a cache with more data can easily solve performance problems. It is too complicated to get a lot of tables to do this. it is worth the candle.