Hence the staff would be scheduled to work, Mon, Tue, Wed & Fri for the above listed times.
Now the problem...
This works fine, however a client of ours have their staff alternate their working hours each week. Meaning, one week they work this pattern, next week they work another, thrid week its back to pattern 1 etc... Alternating between the two weekly patterns.
Now this is ofcorse accomplished via Table 2 enterning a DateStart & DateEnd, however this means it has to be done either, each week, or processed in advance for the next "X Number" of weeks.
Obviously this is messy since it occupies a row in Table 2 for each week.
I have therefore come to a halt and am not sure the best way to aproach this. I could do 1 of two things (I think)...
1) Add To Table 3 two extra fields for the alternating shift Times e.g:
However I am still not sure how I would be able to tell which rotation week the staff will be on. Meaning do I read fields:
(StartTime & EndTime)
(StartTimeAlt & EndTimeAlt)
I might be over looking something obvious thats hammering my head, any assistance would be greatly apreciated...
Could you just have a flag in Table 3 (TblWorkPatterns) that states whether this pattern applies on every week, just odd weeks or even weeks.
Flag -- could contain: every, odd or even
Everything would run as before if the Flag='every' but if the your select would only pull records where the Flag='even' on even numbered weeks and ignore the odd records. Obviously it would do the opposite on odd weeks.
You don't say what type of database you use but all can give you a week number (1-52).
Cycles would allow you to describe repetition easily. The CycleLength would describe how many units of time (weeks in this case) a cycle lasted before it repeated itself. The CycleModulo would describe where in the cycle the week appeared (0 would be the last week in the cycle, 1 would be the first week, 2 for the second, etc). All cycles should be computed from some base date, or you will get into trouble every year when you start the week count over because you'll have two odd weeks in a row. For patterns that repeated every week, the CycleLength would be 1 and the CycleModulo would be 0. A two week cycle would have two rows (2, 1) and (2, 0).
You could then add the CycleID to each WorkPattern in order to tell which cycle applied to that pattern.