Designing Employee Pay & Hours Tables

ICG
ICG used Ask the Experts™
on
I am building a db to consolidate numerous reports currently built in Excel.  Almost all of these reports are based on two spreadsheets updated weekly (employees turn in time once per week - not daily).  The spreadsheets list many details including the consultant, the client site, the account manager (for commisions to be paid), how many hours are worked each week,  the invoice rate we charge the clients and hourly payrate.  I have tables built for employees, clients, pay, etc... but I am unsure how to build the hours/week table.  Do I need 52 seperate columns for each week of the year?  Or is there a better way to build this table?

The attached spreadsheet shows a glimpse of the detail I need.  Obviously (for normalization), the empID and clientID would pull from those appropirate tables.  The hours table (or whatever name is appropriate) should reflect every client-billable hour worked for each week of the year.  I can break down per day if neccessary (I can see benefits both ways as 99% of time clients only care about weekly totals.  However, for long-term planning, it would be nice for employees to enter time daily).

* A FEW NOTES*
- Consultants can work at multiple locations in the same week.
- Consultants can have two different invoice rates for two different clients
- Consultant pay rate does not change/client.  It may change once a year (stored historically in pay table)
Hours-Demo.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
"Do I need 52 seperate columns for each week of the year? "

No, please don't do that!  When normalizing data, it's stored in with minimal redundancy.  Storing the same data in multiple columns is very redundant!  From what I read, I would have a single table with empID, clientID, DateWorkWasPerformed and NumberOfHours.  Obvously, feel free to change the names of those columns to something else if you like.  The point is you only have to store the number of hours that an employee worked for a specific client on a specific day.

Greg


ICG

Author

Commented:
JestersGrint - so you are suggesting that the hours table look like the attached Excel document?  So there would be no primary key in this table as most of the identifiers would be foreign keys (empID, clientID, acctOwnerID, projectID, etc...).  Obivously dateHrsWorked and numHrs could not uniquely ID any record.  This would require hours to be entered daily (not so much technical difficulty but more so training difficulty for the pay dept!).
Hours-Demo-revised-.xls
Your primary key would be empID, clientID and Date assuming that you would want those to be unique.  If the same employee can work for the same client on the same day but on different projects, then you need to add projectID as well.  

I see your point about the pay dept not being happy about putting hours in daily instead of weekly, but in my experience, it is better store things at the least common denominator.  It makes everything a lot simpler when you want to aggregate the data later.  Maybe most reports are weekly, but what if in the future you need to create a monthly, or quaterly report where a week spans two time periods?  It'll be a lot easier if you have the data in days.

Greg


ICG

Author

Commented:
A few issues concerning what are the keys in this table - but almost everything was more clear after explanation.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial