Design issues of crm system statistics table

Currently working on a crm statistical function, we need to create related tables to store various data related to users.
For example: Count the number of new customers added by an employee on that day.
First design a statistical table to store the number of employees’ customers every day.
Then when an employee adds a customer, add 1 to the corresponding field in the statistics table. When a customer is deleted, the corresponding field in the statistical table is reduced by 1.
This triggers the operation of adding 1 and subtracting 1, and uses swoole to execute it asynchronously.

Recently, it is necessary to add the function of counting statistics for this week, this month, this quarter, and this year.

I copied four copies of the [Daily Statistical Table], corresponding to the [Weekly Statistical Table], [Monthly Statistical Table], [Quarterly Statistical Table], and [Yearly Statistical Table].
The data structures of these five tables are similar.

My current question is:
1. Is my previous method of using asynchronous triggering to count the number of daily customers of users optimal? Is there any other better way?
2. When counting weekly, monthly, quarterly, and yearly data, is it better to calculate it from the [Daily Statistics Table], directly build these four tables, or use asynchronous triggering to directly store relevant data?

Is there any expert who can give me an answer? Thank you very much

Reply content:

Currently working on a crm statistical function, we need to create related tables to store various data related to users.
For example: Count the number of new customers added by an employee on that day.
First design a statistical table to store the number of customers the employees have every day.
Then when an employee adds a customer, add 1 to the corresponding field in the statistics table. When a customer is deleted, the corresponding field in the statistical table is reduced by 1.
This triggers the operation of adding 1 and subtracting 1, and uses swoole to execute it asynchronously.

Recently, it is necessary to add the function of counting statistics for this week, this month, this quarter, and this year.

I copied four copies of the [Daily Statistical Table], corresponding to the [Weekly Statistical Table], [Monthly Statistical Table], [Quarterly Statistical Table], and [Yearly Statistical Table].
The data structures of these five tables are similar.

My current question is:
1. Is my previous method of using asynchronous triggering to count the number of daily customers of users optimal? Is there any other better way?
2. When counting weekly, monthly, quarterly, and yearly data, is it better to calculate it from the [Daily Statistics Table], directly build these four tables, or use asynchronous triggering to directly store relevant data?

Is there any expert who can give me an answer? Thank you very much

How many customers do you have? If it's one million levels or less, if it's me doing it, I can just keep it in a table. There are two fields in the table, one is called created_at and the other is called deleted_at, both are of datetime type, and then these two fields are indexed.

In the future, you can query in real time when doing statistics. The speed is very fast below one million levels, and you can check whatever range you want. No matter how much data there is, adding a cache can easily solve the performance problem. It is too complex to make many tables to do this, and the gain outweighs the loss