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:
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.
The table [hours worked daily] is not normalized. Its structure should be something like:
ID (foreign key to [Contact] table)
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.