Designing a leave system for an HR application

Question

Hello,

I am currently developing an HR module for a company. Now, I got stuck designing the leave system. Particularly I got stuck at the carrying balances forward

Well, the leave system is quite simple at this point, each employee has a 30 days leave per year (starting from the hiring date). The leftover days are carried forward to the next year. After the second year, everything settles down and the cycle starts over.

The leave table in my DB looks like this:

leave_id(PK)
emp_id(FK)
date_from
date_to
status
comment

My employee table has a field called (leave_days) that specifies the total leave days that employee can take.

My problem is how to calculate that number. How can I check if an employee is eligible for a leave on a specified period?

I am sorry if it seems a lot to ask. I'll appreciate any answer.

Mahmoud Younes

Answer 1

This does not sound like a complicated problem to me. I think you just need to think this over a bit more. My suggestion: put in boolean flags in your DB to check whether or not the 2 year period has been reached then do the necessary calculations then clear the flag again. You just have to be clever how you apply those flags.

It's also a good idea to make a backup table ie. leave_logs, so in case something goes wrong you can just revert back to this table.Use a trigger on the leave table to populate the logs table. Hope this gives you some insight.