If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Timesheet design help
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 |
#2
|
|||
|
|||
Timesheet design help
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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|