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.
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.