SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to Structure MySQL Tables for a Timesheet

    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
    Attached Files Attached Files

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •