New tables for each day, week, month

[fryertuk001]

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. I have also asked on digital point.

 

[IPGuru]

I dont know the structure of the app but what you are describing sounds terible

the whole point of a db is that you can specify the range of the data to be retrieved.
you probably do need seperate tables but not for Days, months and years as you describe
a sensible structure woul have tables for employees, Pay rates, projects & hours worked & use joins to link the relavant parts when necessary




Do things on the cheap & it will cost you dear

 

[SkipVought]

Hi,

You do not understand databases. What you describe will be a virtual nightmare and an albatross around the neck of the owner of this application.

The structure that you seem to be describing is a calendar table. Every company using a computer to manage their business has primarily ONE calendar table from which they calculate such things as you describe for employee attendance, which would reside in separate tables.

I've worked for several large aerospace manufacturing companies. They ALL had A calendar table that contained past and future days, weeks, years, accounting days, accounting weeks, accounting years, manufacturing days, work days, holidays. ONE TABLE FOR ALL TIME.

You need to educate yourself regarding database design. Else, if you persist in your intended direction, you will find yourself engulfed is an ugly mess!



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[spamjim]

...i would like to keep the table size as small as i can...

What size are you dealing with now? I cannot imagine a performance benefit from your plan.

...then simplify the data to another table for the year...

Are these day, week, & month tables to contain summary data...or the same data that was in the main table? If these proposed tables are not to summarize, look to generating views instead of generating tables (or just craft time based queries in your web app).