I am intending to build a database to track leave of contract employees. Contracts have a start and end date which differs from the start and end of the calendar year.
With respect to leave, there are several types of leave namely: Vacation, Sick; Personal and Maternity.
With the exception of sick leave, the quantum of leave that an employee is eligible for is based on the contract period.
However, with respect to sick, the employee is eligible for 14 days each calendar year. For example, if an employee has a contract for 3 years; in every calendar year that is contained within that contract period, the employee is eligible for 14 days. At the end of a calendar year, unused sick leave does not carry over to the ensuing year.
My challenge.
I am thinking of using one table for leave eligibility, but since sick leave eligibility is based on calendar year and not the contract period then I would need a way to indicate the calendar year and leave eligibility. My thinking is that I could have a sick leave table that is used to track eligibility.
I would appreciate comments on my suggestion to solve the issue, and I am open to any suggestions.