ERD Design for Employee Leave

Question

I found the following ERD design:

Which trying to capture as much information about employee leave (i.e sick, travel, etc). What am trying to understand is what does the columns:

Table : Employee_Annual_Leave

• year_number // Why is this set as the PK? What does it mean by
• year_number der_leave_cumulative_days_taken // where could it be possible be derived from?
• der_leave_days_remaining // and how does these two column work in real scenario?

PS: Yes i have read the "Note" box written above it. But i still dont get it.

Could anyone explain what it means?

Answer 1

To answer your questions:

1. Year number is part of a (three column) composite primary key. There will be one record per employee + year + type of leave (vacation/sick/...) Year number probably means the calendar year stored as an integer.

2. der_leave_cumulative_days_taken means the total of leave actually taken, which would be derived by summing the days between Employee_Leave_Taken.leave_from_date and Employee_Leave_Taken.leave_to_date where those dates fall within the year given by Employee_Annual_Leave.year_number for the same employee_id and leave_type_code.

3. der_leave_days_remaining is next to pointless because it would be Employee_Annual_Leave.leave_days_allowed less der_leave_cumulative_days_taken. A real scenario would be that an employee is allowed three weeks vacation (15 days) and has taken two weeks so far (der_leave_cumulative_days_taken=10 days) and so they have 5 days remaining. The point of keeping these precalculated in this way is that you can refer easily to these values in a query or report. Notice the date_updated field. This implies that there is a regular (perhaps scheduled) batch job that calculates these derived values periodically, or perhaps they are recalculated when changes are made to either leave allowed or leave taken.