Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Posts
    1

    Question Database Design Help

    Not your typical newbe sorta help, but at this point I sure do feel like one.

    Hopefully this will make sense.

    Table 1
    TblStaff
    ID
    Firstname Surname


    Table 2
    TblStaffWorkPatterns
    StaffID
    PatternID
    DateStart
    DateEnd


    Table 3
    TblWorkPatterns
    ID
    DayOfWeek
    StartTime
    EndTime


    Table 2 hold a historic record of the workpattens each staff has been assigned to over the course of their life. If a NULL valude exists in DateEnd, it assumes it as still currently running.

    Table 3 hold the working patterns for each working day the staff is scheduled to work, similar to the following below:

    Monday 12:00 17:00
    Tuesday 13:00 18:00
    Wednesday 14:00 15:00
    Friday 09:00 15:00

    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:
    -StartTimeAlt
    -EndTimeAlt

    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)
    or
    (StartTimeAlt & EndTimeAlt)

    I might be over looking something obvious thats hammering my head, any assistance would be greatly apreciated...

    Thanks...

    ijwalla

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

    Code:
    Table 3
    TblWorkPatterns
    ID
    Flag               -- could contain: every, odd or even
    DayOfWeek
    StartTime
    EndTime
    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).

    Mike

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I would add a new table like:

    TblCycles
    CycleID
    CycleLength
    CycleModulo

    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.

    -PatP

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally posted by Pat Phelan
    you will get into trouble every year when you start the week count over because you'll have two odd weeks in a row
    Good point. I guess you could get round that by getting the number of days since 1 Jan 2000 and then divide by 7 but ...

    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •