Database Design – Integrating Holidays and Attendance in HR Management Systems

database-design

I'm developing a small HRMS system for my company, and am currently thinking of how to integrate attendance and holidays. We currently have an in_time/out_time based daily attendance system, but the HR (naturally) also wants to be able to define holidays, special days off, etc.

Broadly, I can think of the following two tables:

attendance:
    emp_id,
    date,
    in_time,
    out_time,
    att_count (1, 0.5, etc.),
    type (weekend, holiday, etc.),
    comments (if needed)

holidays:
    date,
    att_count(1, 0.5, etc.),
    comments

Here's where I'm stuck: The HR has to be able to define holidays for existing attendance also. In other words, on the 30th of that month, he/she might say, "Oh, the 13th was a holiday but I forgot to include it in our earlier list. Let me add that now." In such a case, I'll need to scan every record existing in the attendance table to see if the date matches. Although our company is small (30-35 people), is this a good strategy when designing a database? What are the best practices you can recommend?

Best Answer

I feel like "best practice" here would be for HR to define the holidays before they happen, or at least before employees submit time sheets against those dates. After the fact, HR has to go to each employee that claims to have worked on that day and determine if they actually worked on the holiday, or if they incorrectly submitted their hours as if they had worked the whole week.

Your system can do a lot to help HR with this.

Most holidays are entirely predictable, as are weekends, as is any interaction between the holiday and weekend (example: if a holiday occurring on a weekend causes the next work day to be a "holiday"). Just calculate all such dates for them, and populate them in your holidays table.

If "special days off" are predictable (example: every 17th Tuesday), do as for holidays.

Some special days off might not be entirely predictable, but might follow a pattern, and your system could warn HR of the upcoming special day, and that they need to enter it as soon as they know the exact date. An example of this might be if you offer a special day off at the end of Ramadan; you know when Ramadan can start, and once you know when it started you know when Ramadan can end, but it would be up to HR to input the dates (or your application might be able to automatically pull it from some other source).

So all that should ever cause issues are unpredictable special days off. HR just needs to be on the ball about them. Maybe have the system prompt them a few days before the last work day of every month, or every week, depending on how far in advance they might know about the unpredictable holidays.

They key take away from this is that HR should be relied on as little as possible to populate holidays, and that where they are responsible they should be reminded, and then it's up to HR to fulfil their role (defining the remaining holidays/special days off).

Related Topic