Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606

    Scheduling System Design

    Quick run-down of what I'm trying to do. We have a scheduling system for computer lab employees to determine when they are working.

    The goal is to create a system that an admin-type can create a work-schedule which is to first enter a generic work week (e.g. Mon-Fri 7AM-11PM, Sat-Sun 11AM-11PM). Then if there are any holidays or overnighters or anything which needs to change the open hours they can go and set up a specific date (such as April 1, 2003 -> 8AM - 5PM, 9PM to 11PM).

    Then any employee can set his/her weekly hours based upon the aforementioned schedule. So if Mondays are open from 7-11 and no one else has claimed a certain block of time (I am almost positive this will have to be done in the application and not in SQL) they can 'claim it' and set their hours appropriately. Obviously for 'special dates' like Apr1 above there will be no checking to see if an employee goes out of bounds -- it is simply for display purposes on the font end ("What does my work week look like this week? Oh, I come in at 11AM instead of 10 since we are opening an hour later.")

    Legend:
    Table( Primary_Key, Foriegn_Key, Col1 )

    Here are the tables I have:

    -- Stores schedule info. Name would be something like 'Winter 2002' and type would be 'Full Time', 'Part Time', 'Seasonal', 'Global' etc.
    Schedule( Schedule_ID, Schedule_Name, Type )

    -- Stores schedule to staff relation. If, for example, I have 40 staff members with the same schedule I don't want to create 40 schedules for them.
    Staff_Schedule( Staff_ID, Schedule_ID )

    -- Schedule Detail adds a day-of-week detail to a schedule. Day-of-week is Monday thru Sunday. Schedule_ID and DayOfWeek combo is a candidate key.
    Schedule_Detail( Schedule_Detail_ID, Schedule_ID, DayOfWeek )

    -- Schedule Detail Date provides the overriding date-specific schedule
    Schedule_Detail_Date( Schedule_Detail_ID, Detail_Date )

    -- Schedule Detail Time will allow the admin to set up their particular time chunk for a detail. For example if the lab is open from 7AM to 12PM, an hour break for lunch, and then from 1PM to 6PM, I would have two rows in this table)
    Schedule_Detail_Time( Shedule_Detail_ID, StartTime, EndTime )

    Question #1: Does this look like a decent design?

    I am having trouble formulating the (Oracle 8) SQL to grab the master schedule for a particular week.

    If I wanted to get the default schedule, I think I can do something like this:
    Code:
    SELECT *
      FROM schedule             s,
           schedule_detail      sd,
           schedule_detail_time sdt,
     WHERE s.TYPE = 'Hourly' -- for lab staff, whatever it is
       AND sd.schedule_id   = s.schedule_id
       AND sdt.detail_id    = sd.schedule_detail_id
    And if I wanted the master schedule adjusted for this week I could do something like this:
    Code:
    SELECT *
      FROM schedule             s,
           schedule_detail      sd,
           schedule_detail_time sdt,
     WHERE s.TYPE = 'Hourly' -- for lab staff, whatever it is
       AND sd.schedule_id   = s.schedule_id
       AND sdt.detail_id    = sd.schedule_detail_id
       AND sdd.detail_id (+)= sd.schedule_detail_id 
       AND sdd.date BETWEEN '20 Apr 2003' AND '26 Apr 2003'
    The problem is that I'll get rows for the default day-of-week and the overriding date.
    Question #2: How would I avoid getting the rows from the default and only the overriding day?

    Question #3: Any other comments on the design/SQL?


    Thanks!
    Thanks,

    Matt

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    everything looks fine, except it's really hard to get a full mental picture of the entire design because there are no example rows

    the part about "claiming" hours wasn't too clear

    if there are 40 staff in a store and the store has a set schedule, wouldn't there be some overlap? is that what you're trying to do?

    willing to help but cannot quite wrap my head around it all yet...


    rudy

  3. #3
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Just the man who I was hoping could look at it!

    Well, here's a few rows in each table:
    Code:
    Schedule
    ------------------
    1, 'Hourly Master Schedule',   'HM'
    2, 'Hourly Employee Schedule', 'HE'
    3, 'Salary Master Schedule',   'SM'
    4, 'Salary Employee Schedule', 'SE'
    
    
    Staff_Schedule
    ------------------
    2, 'MattR'
    4, 'r397'
    
    
    Schedule_Detail
    ------------------
    1,  1, 'Monday'
    2,  1, 'Tuesday'
    3,  1, 'Wednesday'
    4,  1, 'Thursday'
    5,  1, 'Friday'
    6,  1, 'Off Day'
    
    7,  2, 'Monday'
    8,  2, 'Tuesday'
    9,  2, 'Wednesday'
    10, 2, 'Thursday'
    11, 2, 'Friday'
    12, 2, '24 Hour Day'
    
    13, 3, 'Monday'
    14, 3, 'Tuesday'
    15, 3, 'Wednesday'
    16, 3, 'Thursday'
    17, 3, 'Friday'
    18, 3, 'Custom Date'
    
    19, 4, 'Monday'
    20, 4, 'Tuesday'
    21, 4, 'Wednesday'
    22, 4, 'Thursday'
    23, 4, 'Friday'
    24, 4, 'Custom Date'
    
    Schedule_Detail_Date
    ------------------
    6, '12Mar03'
    6, '22Apr03'
    
    12, '12Mar03'
    12, '22Apr03'
    
    
    Schedule_Detail_Time
    ------------------
    1,  '12AM', '5AM'
    1,  '7AM',  '8PM'
    1,  '10PM', '12AM'
    ...
    5,  '12AM', '5AM'
    5,  '7AM',  '8PM'
    5,  '10PM', '12AM'
    
     
    7,  '7AM',  '8PM'
    ...
    11, '7AM',  '8PM'
    
    12, '12:01AM', '11:59PM'
    
    11, '8AM',  '5PM'
    ...
    20, '8AM',  '5PM'
    Thanks,

    Matt

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    After looking at the data (and trying to figure out how everything would work) I think it would make sense to drop the staff_schedule relation and instead add their ID to Schedule (and make it NULLable for the master schedule or create a master schedule table?)

    Schedule( Schedule_ID, Staff_ID, Type )

    With a separate master schedule table Staff ID could also be the PK and I wouldn't really need a schedule table at this point since each employee would get their own schedule.
    Thanks,

    Matt

  5. #5
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Originally posted by r937
    the part about "claiming" hours wasn't too clear

    if there are 40 staff in a store and the store has a set schedule, wouldn't there be some overlap? is that what you're trying to do?
    Yes, there can be overlap. Ignore what I said.
    Thanks,

    Matt

  6. #6
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Hmm, it appears to work OK this way, although I am not convinced that it is the best option. For example, to store a particular users' schedule I have to create a whole new one... Hmm..
    Thanks,

    Matt

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by MattR
    Hmm, it appears to work OK this way, although I am not convinced that it is the best option. For example, to store a particular users' schedule I have to create a whole new one... Hmm..
    It's not clear to me whether (a) there are a small number of fixed schedules, and every employee is allocated to one of those schedules, or (b) there are a small number of "template" schedules, but each employee's own schedule could be amended and be different to any template and any other employee's schedule.

    If (a) your original design was right; if (b) your new design is right - and necessarily means that you have to create a schedule for each employee, since they are all (potentially) different.

    I suppose you could have a hybrid design where each employee is linked to a template schedule, but may have personal overrides like this:

    Staff_Schedule
    ------------------
    2, 'MattR'
    2, 'Tony'
    4, 'r397'

    Staff_Override_Detail
    -------------------------
    'Tony', 'Monday'

    Staff_Override_Time
    -------------------------
    'Tony', 'Monday', '10AM', '3PM'

    i.e. my schedule is just like yours, except I work shorter hours on a Monday. This saves on data storage, but makes it harder to query an individual employee's schedule.

  8. #8
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    It fits option (b) more than (a).

    (Edit: Holy cow, 800 posts! Congrats!)
    Thanks,

    Matt

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm confused

    not that this is news, eh

    going right back to your original requirements, "create a system that an admin-type can create a work-schedule" it would seem that you should not have a table of templates at all, but simply allow each store to set its own schedule for each day

    that would be 7 rows per store (one per day, e.g. monday 7am-9pm, tuesday 7am-10pm...)

    have a store calendar table, to post overrides to those schedules, e.g. "the downtown store will be closed sat april 26" (because the power utility is replacing the transformer), and this would be one row per exception (of which there should not be many)

    thus, checking store open hours into the future is simple (more or less)

    the tricky part is what do you intend to allow individual workers to select in the way of shifts?

    seems to me that if Jonesy, a part-time worker, can select really oddball shifts, such as tuesdays from 5pm-10pm and thursday from 7am-noon, then the number of combinations here is ridiculously large

    on the other hand, if a worker's shift is seen in the same way as a store schedule, i.e. max 7 rows, then you have the same structure (and might even be able to use the same admin code, eh)

    so if you decide the structure first, then you can test it against the types of queries you need to answer

    one you mentioned had to do with workers "claiming" certain chunks of time, but that would be simple, yes?

    well, it would, except if the chunks claimed are by the hour, rather than by the shift...

    rudy

  10. #10
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    The admin sets a template schedule which lists a particular location's availability.

    This is presented to the user (we are open from 8-5 M-F, 12-5SatSun).

    The staff are primarily part-time college student labor so they will hardly ever work an entire shift. They generally are available in small time periods throughout the day.

    So the student takes a look at the master schedule and says that they are not in class (and can work) from 8-11, 3-5 M-Sun.

    The admin then takes that knowledge and intersects the two schedules to create the 'actual' schedule that the student can see.

    Another rub is that if I work from 1PM to 5PM and have a doctor's appointment I need to get someone to work that shift. Actually, I can have any number of people (up to 4 in this case) 'take' these hours. This, I figure, will be the main utility in the schedule_detail_time facility (so for that 1-5 period of time I need someone else to work I can just attach a new detail record with that particular date and then a bunch of time shifts).

    In closing, maybe I need two tables to hold the time information. One for the master template and one for the actual schedule so I can assign employees to it?

    e.g.
    Template_Schedule_Detail_Time( Schedule_Detail_ID, StartTime, EndTime )
    Actual_Schedule_Detail_Time( Schedule_ID, StartTime, EndTime, Staff_ID )
    Thanks,

    Matt

  11. #11
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    So perhaps a revised table structure (if I want to be sneaky and combine everything):
    Schedule( Schedule_ID, Schedule_Name, Type )

    Schedule_Detail( Schedule_Detail_ID, Schedule_ID, DayOfWeek, OverrideDate )
    OverrideDate can be null

    Schedule_Detail_Time( Schedule_Detail_ID, StartTime, EndTime, Staff_ID )
    Staff_ID can be null
    Thanks,

    Matt

Posting Permissions

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