Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2014
    Posts
    2

    Online roster DB design help

    Hey All,

    Hoping I can get some help with my Database design. I'm currently building a website for internal use for work colleagues where they can log in to view their roster and make requests to swap shifts, ask for leave etc. 1 person has access to create and delegate shifts (I would also like to make this semi automatic, as the shifts rotate, where you setup a pattern and then apply it for up to a year or so) and approve leave/shift swap requests.

    1 thing with our roster that I am struggling to work out how to implement has to do with our on call shift. An example of the roster is below


    Week| Empl 1 | Empl 2 | Empl 3
    MON | 9 - 5 | day off | 9 - 5
    TUE | 9 - 5 | 9 - 5 | On Call
    WED | 9 - 5 | 9 - 5 | On Call
    THUR| 9 - 5 | 9 - 5 | On Call
    FRI | day off | 9 - 5 | On Call
    SAT | day off | 7 - 3 | On Call
    SUN | 11 -7 | day off | On Call
    MON | 9 - 5 | 9 -5 | On Call
    TUE | 9 - 5 | 9 - 5 | 9 - 5
    WED | 9 - 5 | 9 - 5 | 9 - 5
    THUR| 9 - 5 | 9 - 5 | 9 - 5
    FRI | 9 - 5 | 9 - 5 | 9 - 5
    SAT | day off | day off | day off
    SUN | day off | day off | day off

    sorry for my archaic table

    As you can see the On call shift leeks into the following week. I have mocked up a DB how I think it should be however, I'm not sure of the relationships, also if anybody has a better design I'm happy to take it on.

    User Table
    Employee_ID(PK) - First_Name - Last_Name - Ph_number - Access_lvl

    Shift Table
    shift_ID(PK) - start_time - end_time - description - display(what gets displayed on the roster)

    Scheduled Week table
    Week_ID - shift_ID(FK) - weekday (I believe all 3 of these should make up a composite key as all 3 columns can have duplicates)

    Scheduled Day table
    Employee_ID(FK) - date - shift_ID(FK) (employee ID and date should be a composite key?)

    I hope this makes sense, if anybody needs further clarification I'm happy to provide it.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Calendaring is one of the more difficult modeling tasks.

    Dump your Week table. Days are days, and what week they are in does not matter for your purpose.

    Be glad you shifts do not span days.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2014
    Posts
    2
    Thanks.

    With the week table I was thinking of using it to define weekly shifts so I can automatically fill out the roster, so user defines each available shift and then the script can rotate through each available week assigning it to each employee, rather than the user having to manually set each day for each employee.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    If your shifts span weeks, then they are not based on weeks.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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