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
http://mvp.support.microsoft.com/
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
"Squid" <Squid[ at ]discussions.microsoft.com> wrote in message
news:5235D61F-327A-4845-8A78-6D4FC5969A6A[ at ]microsoft.com...
[Quoted Text] > 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
>
>
|