In layman's words:
Use surrogate primary keys as primary keys when:
- There are no natural or business keys
- Natural or business keys are bad ( change often )
- The value of natural or business key is not known at the time of inserting record
- Multicolumn natural keys ( usually several FK ) exceed three columns, which makes joins too verbose.
If a natural key exists that doesn't fall into the conditions listed above, for the sake of God, use it, especially if the key is an ISO standard or is issued by some respected institution, like country codes, airport or airline IATA codes, MAC addresses, car plaque numbers, IMDB movie codes, radio station call signs, etc. That would allow you to interoperate easier.
Above conditions mean surrogate keys will have to be used in many tables. But not all.
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).
Best Answer
What you have is not flexible enough.
The basic requires are you need to be able to express e.g. the following:
One simple way to express this is to add fields for the possible ways to define holidays:
This is not ideal because one can easily put bad data in (e.g. Monday, first week, and 25th day), but it is simple and easy to understand.
There are other ways to define holidays that I have seen elsewhere, but they basically require implementing a rules engine which is very complex and easy to misuse. These behemoths have a bad reputation that is well-earned, but sometimes they really are the best tool for the job.