Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2011
    Posts
    27

    Unanswered: Database Design for Employees and Contractors

    I have created a work hours tracking database but am running into problems. I'm looking for database design ideas.

    My main table needs at least this much information.

    Table: Work
    Code:
    WorkID (autonumber)
    ProjectFK (which project)
    CodeFK (what they did like Coding or Marketing)
    PeopleFK (who did the work)
    DateWorked (date worked)
    HoursWorked (hours spent by that person on that project doing that code)
    The rest is up for change. I track employee promotions and their internal rates through time. The biggest wrench is that I track contractor hours through the same system (not my idea). I need to process fixed rates into hourly rates, or just accept an hourly rate. The contractor rates are different and are defined by the fields PeopleFK (contractor), ProjectFK (which project), and CodeFK (different rates for same project but doing different things i.e. travel or programming).

    Reports would aggregate all types of hours (employee, contractor hourly, and contractor fixed rates) in all sorts of combination and permutations.

    I would like your input on how you would go about creating (actually redesigning) this database. Let me know what other information you need. I look forward to your ideas!

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    first I would store the MINs work not hours ( hard to get head around)

    ie 1.5hrs + 10 = 2hrs or 1:40

    If your PeopleFK is link to a People table then in that table I would have a Type or worker "Contractor","EMP"

    then you be able to group by the Contractor or EMP

    then have another table with [codeFK] and [type of worker] [$$$] then you can link then in querys you be able to get the $$$ out times by the MINS/hours
    hope this help
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by Phasma View Post
    The biggest wrench is that I track contractor hours through the same system (not my idea).
    Well whoever decide they should be together really new what thyy were doing. That is the best way to do it. Kudos to them.

    A person being a Contractor or an Employee is an attribute of the person. It is not a different type of object requiring a different table.

    Can a person got from being a Contractor to an Employee or the other way?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Tags for this Thread

Posting Permissions

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