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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 Apr 26 '14 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 '14 at 18:03
Yes thanks a lot – VAAA Apr 26 '14 at 18:06

Your Answer


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

Browse other questions tagged or ask your own question.