Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2011

    Unanswered: Trying to Figure out Tables to Use

    I have a question that I need to answer with a bunch of criteria for my database. I have tried to figure out how many tables I have to use and what data to put in each one but have been very confused. Here is the criteria:

    -Track employee contact information, including name, phone number and address

    -Track employee payroll information including hourly rate

    -Log days/hours each employee has worked

    -Track gross sales on an hourly basis (total dollars per hour)

    -Provide a weekly payroll report showing each employee, their total hours worked and gross pay for the most recent pay period (Sunday through Saturday)

    From this I have created two weeks of data (Mon.-Fri. for two weeks) (all 5 employees worked 8 hours a day)

    I have the contact table:
    ID (PK)
    Last Name
    First name

    payroll table:
    Hourly Wage (PK)
    Days worked (total two weeks)
    Hours worked (total two weeks)

    hours worked daily:

    That is what I assumed but I don't think I'm right. Please help. I am lost. It looks for a lot of hourly data, but I feel that involves more tables than it should.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    The table [hours worked daily] is not normalized. Its structure should be something like:
    ID (foreign key to [Contact] table)
    Hours_Worked (Decimal)
    Day_Number (Integer with a constraint 'Between 1 And 5' or 'Between 1 And 7' if employees can work any day of the week).
    I'd prefer to have a [Date of Service] (Date/Time) field, though.

    As for the [Payroll] table I don't see why you need it. [Wages] can be included into the [Contact] table (or you can create a [Wages] table if necessary, for instance if wages change according to the day of the week). [Days worked] and [Hours worked] are computed fields and should only appear in a query.
    Have a nice day!

Posting Permissions

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