I have designed a MySQL database for an employee monitoring system, it was working perfectly well until I was asked to put a new work schedule for some of the employees.
The new schedule was what they called a broken schedule because an employee would have different shifts in one day, for example, an employee could be given a work schedule for a given day starting from 7:00AM-11:00AM and then continues at 3:00PM-7:00PM...
The problem I encountered now is, I am confused on how to keep track if the employees are late of not now that some employees have two or more work schedules...
Could anyone help me pls?
for more clarity, lets say we have employee A. employee A for example has the following work schedule for a given day - 5 days in a week.
7:00AM-11:00AM - shift 1
3:00PM-7:00PM - shift 2
9:00PM-11:30PM - shift 3
then, lets say employee A logged in at these different scenarios:
scenario #1: logged in at 7:10AM
scenario #2: logged in at 2:30PM
how do I check that employee A's log info at scenario #1 is not referencing shift2 or shift3?
likewise, how do I check if employee A's log info at scenario #2 would only reference shift2 only and would not make him/her late at his/her schedule at shift #1 or too early at shift #3?