I'm starting a time and attendance module in C# and SQL.
There will be many time rules depending on every shift.
Shift 7:00 - 14:00 for Administratives: 15 min grace time, but if within a month there are 3 or more Late Arrivals then half day is discounted.
Shift 7:00 - 14:00 for Administratives: between 1 and 30 minutes of Early Leave discount 30 min of working hours.
Shift 7:00 - 14:00 for Maintenancce: 10 min grace time, but if within two weeks there are more than 3 Late Arrivals the day is marked as absent (no working hours even if they stay)
Shift 23:00 - 6:00 for IT (Overnight shift): 15 min slog time (so they have to enter 22:45). If within a week there are more than 3 Early Leaves then discount half day.
And many other rules.
The process starts by importing a Timesheet from an access control system. Then, imported data is stored in a temp database in SQL. Then, the imported data is matched with employees table and then inserted into a employee_timerecords table.
A background process should start in order to loop employee by employee, day by day and applying all the rules depending on the employee shift, employee group, etc. So I was thinking in a Employee Exceptions table in order to store every rule that is broken or every condition that match, so later I can query and know how many hours to discount, how many late arrivals the employee have, etc. So my proposal schema should like:
So what I should get in Employee_Time_Exception table should look like this:
Does it make sense or any recommendation?