How to Structure MySQL Tables for a Timesheet

[rcap28]

Hello,

I am thoroughly stumped as to how to efficiently structure the tables for a timesheet, in a flexible manner... There are also other aspects to the project that I am working on, that I am having difficulty with, but I figured this would be the best place to start.

It is a mysql database, and is being accessed through PHP. The original timesheet is attached, and needs to be flexible regarding the number and names of employees worked, days and hours worked, and the dates associated with the timesheet.

Each week, I need to be able to pull a timesheet, according to its week ending date and the project, that displays the hours of X number of employees that worked on that job, Y number of days from Sunday through Saturday..

Any [detailed] help would be greatly appreciated!

Thanks

[r937]

you'll need a projects table, with project id, name, foreman

you'll need an employees table, with employee id, name

finally, a timesheet table with a primary key consisting of

project id
employee id
date

and data columns of in, lunch, and out

in your example, it looks like "in" and "out" for an employee cover his entire day on a given project -- does an employee ever need to allocate hours in a single day to more than one project?

[rcap28]

Thank you so much for your response r937! Much appreciated...

A couple of things:

1) the foreman on a given project is not fixed, so I wouldnt want to include that field in the projects table...
2) occasionally, an employee will work at two different jobs on the same day... which made me try and think of somehow incorporating shifts into the mix...

Ultimately, a project manager will need to enter the daily hours of each employee in his crew under each job where they worked... Then, others can go back in and search the timesheets (displaying the 7 days of employee hours) according to the week ending date (always a saturday) and the project. I feel like I need a structure including lookup tables, but im just not sure... Here are the project and employee tables I already have:

tbl_project
-id
-name
-contract
-city
-state
-location
-type

tbl_employee
-id
-firstname
-lastname
-roleid
-address1
-address2
-city
-state
-zipcode
-phone
-email

[rcap28]

Oh and also, the project manager will be entering the employee hours on a daily basis... So they will need to access the same "timesheet" after each day worked and update each employees hours worked for the week...