Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm starting a time and attendance module in C# and SQL.

There will be many time rules depending on every shift.

For example:

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:

enter image description here

So what I should get in Employee_Time_Exception table should look like this:

enter image description here

Does it make sense or any recommendation?

share|improve this question

migrated from codereview.stackexchange.com Apr 26 at 18:08

This question came from our site for peer programmer code reviews.

    
This is off-topic as there is no code to review. I believe it would be best for Database Administrators SE. Would you like it migrated there? –  Jamal Apr 26 at 18:03
    
Yes thanks a lot –  VAAA Apr 26 at 18:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.