Timesheet design help

[Squid]

I am having problems normalizing my database. The staticTime table is the
time that the employee receives at the begining each year and was rolled-over
from the previous year. The Time table holds all the transactions.

Employee Table
EmployeeID (pk)
EmployeeName

StaticTime Table
Autonumber (pk)
EmployeeID (fk)
Date (always the first day of year)
BeginningSickTime
BeginningApprovedLeave
BeginningVacation
RolloverSickTime
RolloverApprovedLeave
RolloverVacation
CompensatedSickTime
CompensatedNotes

Time Table
Autonumber (pk)
EmployeeID (fk)
TimeRegular
TimeSickTime
TimeApprovedLeave
TimeVacation
TimeLeaveNoPay
TimeNoChargeLeave
JobCostCCF
JobCostContrib
JobCostMedical
JobCostPension
JobCostSavings
JobCostPerCapita
JobCostOther

 

[Jeff Boyce]

We may not share the same definition of "normalization".

If you store "roll-over minutes" for each year, how do you ensure data
integrity? That is, if it is discovered, after the process of calculating
the rolled-over time, that a calculation error was made, how do you ensure
that the rolled-over time is properly updated? For that matter, doesn't
this approach require annual maintenance? Perhaps you don't need to store a
value that might be calculated by Access 'on the fly' (e.g., in a query).
How do YOU calculate the rolled-over time?

If the fieldnames you are using in your tables are descriptive of the
contents in those fields, it appears you have a large number of "repeating
fields" (e.g., "JobCostXXX", and "TimeXXX"). This design is not
well-normalized ... I'm guessing that if a category of JobCost or Time is
changed (new one added, old one dropped/modified), you'd need to go through
your entire database, fixing table definitions, queries, forms, reports,
code, macros, etc.

A well-normalized design would allow you to add/edit a table entry to make
that change, not change the entire database.

As an example, if you used a single field to hold [JobCostType] and another
field to hold the value thereof, a new JobCostType would go in the
[tlkpJobCostType] table, and be available (via foreign key) to use.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/