I found the following ERD design:

enter image description here

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?


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.