Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2008
    Posts
    6

    Database design - Timesheet

    For many years I had my employees enter their time and job cost information into a spreadsheet. Each employee had their own password protected workbook. Each worksheet was a month and there was also a summary worksheet. There were two sections to the spreadsheet, time and job cost. For each day they would enter their time classification(s) and the department(s) they worked in. The total time must equal to total departments time. There is a column to calcate overtime if the total of time classications exceeded 7.5.

    The following is the columns of the worksheet:
    Date, Regular Time, SickLeave, ApprovedLeave, Vacation, LeaveWithoutPay, LeaveNoCharge, Total Time (calculated), Overtime (calculated). Department1, Department2, Department3, Total Departments (calculated), Proof (Total Time - Total Departments).

    The summary worksheet compiled the information entered by quarter. Also calculated their remaining time per classification. The calculation is time rolled over from previous year + time available current year - time used current year. Rolled over time and time available are static values entered the first day of each year. time used current year is calculated.

    I want convert this into a database. So i can analyze and control the information better. But need some help designing the database. Below is what I have so far:

    tbl_Employee
    EmployeeID (pk)
    EmployeeName

    tbl_Department
    DepartmentID (pk)
    DepartmentName

    tbl_TimeClass
    TimeClassID (pk)
    TimeClass - regular time, sickleave, vacation, etc.

    tbl_BeginningTimeCurrentYear
    TransactionID (autonumber)(pk)
    Year - alway the first day of the year (sk)
    EmployeeID (fk)
    TimeClassID (fk)
    TimeClassHours

    tbl_RolledOverTimePreviousYear
    TransactionID (autonumber)(pk)
    Year - alway the first day of the year (sk)
    EmployeeID (fk)
    TimeClassID (fk)
    TimeClassHours

    tbl_TimeSheet
    TransactionID (autonumber)(pk)
    Date (sk)
    EmployeeID (fk)
    TimeClassID (fk)
    TimeClassHours
    DepartmentID (fk)
    DepartmentHours

    Scenarios:
    1. An employee can have one or more time classifcations per day
    2. An employee can work in one or more departments per day

    Any help or suggestions will be greatly appreciated.
    Thanks in advance
    Mike

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    How many employees do you have? None of them know anything about databases? What kind of business are you in?

    Sounds like RFH to me.....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2008
    Posts
    6
    Small company of 17 employees. i have some employees that i had to recenly teach copy & paste.

    What is an RFH?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    RFH to me stands for Request for Help; except I bet the H in blindmans usage means "Homework"
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    We get a lot of people coming on with homework requirements. It is curious to find a business owner with so many employess getting stuck into the nitty gritty of database design. Ordinarily one would expect your time to be worth more than that of a professional in this area (especially given the learning curve you are on).

    Anyhoo - I would probably not have a current and historical table- just have the one. I also don't understand what DepartmentHours in Timesheet indicates.

  6. #6
    Join Date
    Jan 2008
    Posts
    6
    Even worse yet, I am the controller that's in the middle of year end hell. Now it makes sense why i had a heck of time finding examples on the web. I am using Departments as a generic term for job cost centers.

    The flaw i find in my design is with the timesheet table doesnt allow for an employee to work 7.5hrs and then spread that across several different departments. They would need to enter the time classifcation for each department. For example: I can work a regular 7.5hr day, but i need to allocate 3hrs to Dept1, 2 hrs to Dept2 and 2.5 to Dept3.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by Squid2008
    For example: I can work a regular 7.5hr day, but i need to allocate 3hrs to Dept1, 2 hrs to Dept2 and 2.5 to Dept3.
    Well, there is no getting around that. How could you write any business logic that would know how they distributed their hours? That must be entered by hand.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would expect, however, you to have a single hours field in your timesheet. These hours are then effectively categorised by TimeClass and assigned to the Department cost centre. As mentioned by blindman, if you assign your day's "regular time" to three different cost centres then you have to enter three rows - no way round it (or at least if you db is properly designed at least). You can usually alleviate repetative data entry with your UI e.g. the user enters their identifier one on entry not on every row they enter.

    Also - what is an "sk"?

  9. #9
    Join Date
    Jan 2008
    Posts
    6
    That is correct, the employees allocate their time worked manually.

    So, the database is normalized properly?

    sk = secondary key

    Thanks!
    Boo Steelers, go Pens!

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - the proper term is Alternate Key. And I am afraid you have your alternate keys all wrong

    You have introduced surrogate keys into your database (all the autonumbers certainly). The Alternate keys are what as known as Natural Keys, in so far as they are compossed of information in the real world. They can (and indeed often have to be) a combination of two or more columns.

    To see how your alternate keys are problematic consider tbl_BeginningTimeCurrentYear. Based on your Alternate Key being Year and that Year is "always the first day of the year" - how many rows can you have per year?

  11. #11
    Join Date
    Jan 2008
    Posts
    6
    For each employee and year they will have 3 rows (approved leave, sick leave and vacation)

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not with your current alternate key - you can only have one row. You have pretty well defined your composite alternate key - year, employee, timeclassid. If you maintain your currentyear and previous year you don't even need the year column as part of the PK in the first table since it is current! However, I still think you should have these as a single table with the above ak.

    In fact, I would make that your PK and dump the autonumber but that is just my preference.

    Rudy posted this sometime ago and I think it is my fave online db design article:
    http://www.tonymarston.net/php-mysql...se-design.html
    See how you do.

Posting Permissions

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