Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

New tables for each day, week, month

New tables for each day, week, month

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

RE: New tables for each day, week, month

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

RE: New tables for each day, week, month


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!


glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: New tables for each day, week, month

Quote (fryertuk001)

...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.

Quote (fryertuk001)

...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).

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close