Generalities and exceptions, or repetitive data?

[john471]

Hoping for some assurance and assistance....

I am needing a database that will help to manage employee leave (holiday) requests. I have one but the current design is a bit...... well, you know.

Leave Limits are (presently) applied by one table:-
tblLimit
-------
payWeek
workType (twentysix work types some common to all sites, some apply only to one or two sites)
LimitSiteA (three sites)
LimitSiteB
LimitSiteC
LimitAllSites

So, several "faults" are immediately apparent:

LimitAllSites is ALWAYS the sum of LimitSiteA+LimitSiteB+LimitSiteC so
clearly shouldn't be there, and I don't even want to talk about that....

Also , no primary key, repeating columns... uh-oh!
Therfore....
tblLimit
-------
limitID (PK)
payWeek (date/time - always a Wednesday, our payWeek begins on a Wednesday)
workTypeID (FK to new table tblWorkType)
siteID (FK to new table tblSite)
Limit (integer)


However....with just a brief investigation, it transpires that of the 1600+ rows in this (current version) table, there are only 69 unique sets of limits (ran a Unique Values query, excluding the PayWeek).

So, what I do want to talk about is not having to add in a table row for each WorkType for each PayWeek (for each site- with the improved design). I want to apply a general set of limits, with effectiveFrom and effectiveTo dates, per Site/workType combination, which should automatically populate out 80 weeks into the future (historical records are not required - it is just to help plan future leave) and then cater for the exceptions by "adjusting" the general limits in one of two ways:
1) every day over a date range (e.g. every day between 15NOV05 and 15DEC05, decrease the limit for worktype Z in Site A by 10 percent of the BASE* limit).
2) every nominated weekday over a date range (e.g. every Friday in December 2005, increase the limit for worktype Z in Site A by 20 percent of the BASE limit).

*the two adjustment types need to be independent, overlappable, and have a cumulative effect.

e.g. for the above, if the general limit for type Z of site A were 100
14 NOV --> limit 100 = 100 (no adjustment)
15 NOV --> limit 100-10 = 90 (adjustment per #1)
.
. - repeat concept as above
.
30 NOV --> limit 100-10 = 90 (adjustment per #1)
01 DEC --> limit 100-10 = 90 (adjustment per #1)
02 DEC --> (Friday) --> limit 100-10+20 = 110 (adjustment per #1 & #2)
03 DEC --> limit 100-10 = 90 (adjustment per #1)
.
. - repeat concept as above
.
15 DEC --> limit 100-10 = 90 (adjustment per #1)
16 DEC --> (Friday) --> limit 100+20 = 120 (adjustment per #2)
17 DEC --> limit 100 = 100 (no adjustment)
etc

It gets dirtier !
There are two broad types of leave - general - for which the limit is determined/applied at the (site+workType) level, and "long service" for which the limit is to be applied at just the site level (workType has no bearing).

I was OK up until it got "dirtier" - now I am not so sure....

I was thinking...

tblLimit (base limits)
-------
limitID (PK)
workTypeID (FK to new table tblWorkType)
siteID (FK to new table tblSite)
limit (integer)
effectiveFrom (date/time)
effectiveTo (date/time)


Does my base limit table store WorkTypeID, but as null when the record relates to "long service" leave, and as a valid WorkTypeID when the record relates to "general" leave ? If so, is it of sound design to derive the broad leave type from the "nullness" of the workTypeID, or is it recommended to store that separately?

Similar questions relate to the adjustment table, for both WorkTypeID and WeekdayID...

tblLimitAdjust
-------------
limitAdjustID (PK)
workTypeID (FK)
siteID (FK)
adjustFactor (double)
WeekdayID (FK)
effectiveFrom (date/time)
effectiveTo (date/time)

I was sort of hoping that writing this out may help clarify my mind on some of the points.... which it did, but only to a limited extent so....

Suggestions / corrections / Pointers ???

Regards

John.

 

[L'apprentis]

tblLimit
-------
payWeek
workType (twentysix work types some common to all sites, some apply only to one or two sites)
LimitSiteA (three sites)
LimitSiteB
LimitSiteC
LimitAllSites

So, several "faults" are immediately apparent:

LimitAllSites is ALWAYS the sum of LimitSiteA+LimitSiteB+LimitSiteC so 
clearly shouldn't be there....

You shouldn't store any calculated data, it is like entering twice the same data in your system. What I would do is use a query for that particular situation:
Use a query With all the field you want to keep from TblLimit and add a field That you will call LimitAllSites where LimitAllSites=LimitSiteA + LimitSiteB + LimitSiteC