New tables for each day, week, month.

Hello i have an web app that i have been building for the past couple of years.
Its main role is to record employees attendances and there jobs during the day and then for the office
staff to calculate pays and hours worked on jobs by multiple jobs.
Also the office can see who is workin on what etc.

At the moment each activity if it be logging on or logging off or starting a job or finishing a job or going to lunch or finishing lunch etc.
This web app will be on the local Lan only.
These activities are all stored in a single Mysql table as each on must have an off..

The problem is that i would like to have a new table each day and then each day to be copied to a weekly table each day
deleting the day table once checked..
The weekly tables will align with their pay week. These weekly tables will be kept until a month then simplify
the data to another table for the year.

This part of daily, weekly, monthy table has many ways but i am unsure.
Use mysql event??, scripts from linux (cron or LT script), or other ways??
Best ways to create daily tables and other tables without user assistance.

I am using php as the backend language.

I have used temporary tables in VBA years ago for a point of sale system for corner shops that worked good.

But this php mysql on linux web app will have multiple user accessing and payrol using queries to extract employees hours and jobs and their hours and i
would like to keep the table size as small as i can.

Hope this makes sense.

 

Thanks Sarahk for your reply.
The thinking behind having daily tables for all logging in and logging out and jobs started and jobs finished is that the amount of data collected every day grows in size day after day quickly.
For the different departments, on different browsers, in different places doing quite complicated queries on that data takes time.
Some of the job queries and reports can be over multiple people and multiple times. Some of the admin queries on staff and jobs are built from multiple queries.
By keeping the table size small allows for fast complicated, multiple site requested queries to be performed. Some of this data helps perform near real time updates on jobs and office notifications.

Each day a new table is created and buy the end of that day that data can be transferred to the weekly table. Once transferred to the weekly table and compared this daily table will be deleted and a new table created for the new day. Daily tables need to be super fast in their access as real time reporting (notifications) and query reports by office staff,admins,employees,payroll.
So on any day there will only be one daily table.

The weekly table will run the length of the clients normal pay week. At the rollover of that week it shall contain all the rows from the last seven days or pay week.
The beginning of a new day adds the rows from the previous days data, checks it, deletes it and creates a new table for today.
This weekly table has no real time queries and notifications running against it only job times and employees times.
Once the weekly table have been used and processed they are added to a monthly or a yearly table.

This is a part of this app i have made as it also covers recording of jobs for manufacturing, receiving and ordering of parts and components for jobs. Costing of materials per job. etc

I just cannot get it in my head how to programmatically name(need to record name somewhere), build with reliability and have access for data entry.

 

I'd suggest using a single data table with INDEXES to make it work faster.
Many millions of records can be held and queried in real time without any issue, if indexes are made properly and queries made accordingly.
If you want a multiple table system, then all you need to do is, copy the whole table at midnight in a "weekly_table_data".
Copy all records, full data, as it is.
Then simply drop and rebuild the daily table_data table, which will be empty at this point.
Now you can run weekly queries on the larger weekly_table_data table, and daily ones on smaller table_data table.
Same query, just different table name...
You won't have to make a month table, because your weekly table has all data, so you can query that itself, just change the date in the query.


Example:
table_data
id bigint(18) auto_increment, employeeID int(10), startTime timestamp, endTime timestamp

When user logs in, do a new insert in this table, like:
insert into table_data (employeeID, startTime) values(123, 2021-12-23 09:30:57);

When user logs out or takes a break, then simply update the endTime field in the last record of this user's employeeID.
select id from table_data where employeeID=123 order by id desc limit 1;

update table_data set endTime='your_time' where id=your_ID_from_prev_query limit 1;

They come back, start a new record like in first step.

In this system, you make an index like: (employeID, startTime, endTime)

Your day query will be like this:
select timediff( startTime, endTime ) from table_data where employeeID=123 and startTime>='2021-12-23' and endTime<='2021-12-23';

This query will give all time differences for the whole day, and number of records returned will be equal to how many times the user took a break.

Use PHP to simply add all time differences and display total time and number of breaks taken etc.

In single table system, To get weekly data, you simply change the date in the last query to start on monday and end on friday.
Again you get same kind of resultset, but more records, with whole week data.

There is only one drawback in this system, that is, entire table will get read on each query, because you are not using a "limit".
To solve that, simply add a column in the employee table which tracks how many records are available for the day.
Everytime the user logs off, increment the number.
Then use that number as "limit" in that last query.