Integrating Holidays and Attendance in an HR Management System

Question

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?

Answer 1

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).

Answer 2

The system, as currently defined, is very rudimentary and would require significant refactoring for changes. As this is in a state of flux ("Because our company is still evolving, these are not well defined, unfortunately"), the thing to do is to design with the intent to be flexible. Note that the design with flexibility will come at a cost of longer time to do the design (rather than diving right in and coding).

The first thing that is apparent is there are different types of days. These are currently being stuffed in a field (type (weekend, holiday, etc.)) There are different types of things here again. There are the predictable, and the less predictable.

Weekends are very predictable. Holidays are less so. What this boils down to is you need a function or stored procedure that gives you a set of types of days given a date. This is especially important when the same day can have multiple day types - Dec 25, 2016 is on a Sunday; this is both a weekend and a holiday.

One could do this as isWeekend(date) and isHoliday(date). Or you could have some other function that returns a structure of some sort that indicates all the date types. Or you could stick that information in a table with the appropriate many to many relationship.

I'm also going to point out something that needs to be taken into account. Updating history. Its well and fine to have a function that lets you do isHoliday('Oct 31, 2014') that returns false. But if you also get paid on the the end of the month and on Nov 3^rd HR decides that Halloween is a half day vacation... yea. Things can get messy rapidly. This is an area that the requirements need to get flushed out more clearly. You might want to invest in Mastering the Requirements Process: Getting Requirements Right and consider what the requirements are and how to work with them. This may just be funny money within the system, or it may be that you are punching in and out and working hourly and punching in at 8:15 rather than 8:00 affects your pay rather than just record keeping.

Another thing to consider is the "can a special day belong to one person?" The answer is "very likely." This may range from things like "jury duty" to the policy that a previous employer had of your birthday as a holiday (this is likely different for each person).

Defining a table that contains the special day type works better than defining an enum or set in most cases. The biggest point here is that day types can have additional information associated with them - enums and sets cannot.

So, I'd be looking at something like:

+--------------------+  +----------------+    +---------------+     +----------+
| shift              |  | special_shift  |    | special       |     | type     |
+--------------------+  +----------------+    +---------------+     +----------+
| id                 +--+ shift_id       |  +-+ id            |  +--+ id       |
| emp_id             |  | special_id     +--+ | date          |  |  | text     |
| date               |  | id             |    | emp_id (null) |  |  |          |
| start              |  |                |    | hours         |  |  |          |
| end                |  |                |    | type_id       +--+  |          |
|                    |  |                |    |               |     |          |
+--------------------+  +----------------+    +---------------+     +----------+

If there is no corresponding row in special_shift for a given shift, that shift isn't special. I would have a trigger on special_shift for an on insert that kicks off some other processes (logic for does a paycheck need to be reprinted?)

Within the special table, emp_id may be null. If there is an employee id, this special field is for that employee only. Vacation, sick day, family leave, birthday, jury duty. The 'hours' would be how many hours that employee is credited with for that special day - it may be null. There may also be a rate (weekends are +0.5x, holidays are +0.5x, weekend holiday would then be +1.0x). This allows for triggers to be added for an on insert in the shift table so that if the date matches a row in special_shift can be automatically created.

There is nothing wrong with having a script go in and populate all the weekends automatically so that these are part of the table. Fortunately, you can be fairly assured that weekends do not change after the fact.

You will see that I've gone with the many to many table here. A given shift may have many special dates attached to it, and a special date may apply to many shifts.

This then has a foreign key to the type. Currently, I've just got a text field in there for what it is (vacation, sick day, etc...). However, this may include information such as 'hr approval needed?'

By no means is this meant to represent the final, best answer. But rather give you some things to consider and approaches to take when designing such a system. I'd really suggest sitting down with HR and trying to nail down as much of their logic as possible in plain english before tackling too much of this (if you get some day as a 1.5x pay bonus and have jury duty what happens? Can you use a sick day on a holiday? If you are taking a week of vacation and come in and work, is that over time (yes, this is a real situation)? Do you need approval to work on a holiday or weekend?).

Look around though, there are likely business rule triggers all over and you will need to think about how to handle this. Do you store updates until they are processed? Are the business rules written in the trigger? or some sort of extract / transform / load approach where the business logic is stored in a rules engine?

Designing the payroll tracking is a huge project. There are entire segments of the software industry built around just this problem - and there is a reason that they can charge the amount they do for their software (it's not easy). While you aren't likely going to implement C3, you may wish to read Why Is Payroll Hard.