4

I'm creating a system to store holidays and events. I have a basic understanding of the schema for the static dates (every 23rd of march for instance).

My trouble lies with the relative dates (i.e. : every 3rd friday of march).

For the static dates we can safely have a schema that looks like this :

+----------+--------------+
| Col Name |     Type     |
+----------+--------------+
| Id       | Int          |
| Name     | varchar(255) |
| Month    | Int          |
| Day      | Int          |
+----------+--------------+

How would it be possible to add the relative dates in this schema ?

2
  • 1
    You can't. Can you change the schema?
    – user40980
    Jun 29 '15 at 2:59
  • Since this a new application (mostly for fun and explorations I must admit) everything is possible here.
    – Erick
    Jun 29 '15 at 3:19
3

What you have is not flexible enough.

The basic requires are you need to be able to express e.g. the following:

  • U.S. Memorial Day is the last Monday of May.
  • U.S. Thanksgiving is the fourth Thursday of November.
  • Christmas is the 25th day of December.
  • Easter is... maybe just scrape Wikipedia?

One simple way to express this is to add fields for the possible ways to define holidays:

  • Month
  • Day of Week
  • Week of Month (use negative to count backward for e.g. Memorial Day)
  • Day of Month

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.

2

You've got a holiday and the basic information about it.

Holiday:
  holidayId (pk)
  name

What types of holidays do we have? There are the fixed holidays, the moving holidays and the "I'm not even going to bother calculating" holidays (Easter, I'm looking at you).

For this, you would have subtables.

There are the fixed, repeating holidays. Things like Christmas and the Forth of July. These are holidays that fall on the same day of the month each year.

FixedHoliday
  FixedHoldiayId (pk)
  HolidayId (fk)
  MonthOfYear
  DayOfMonth

Then you've also got the movable ones. Labor day (first Monday in September), Memorial day (last Monday in May), Thanksgiving (forth Thursday in November).

The easiest way to approach this is to give a day range for the day.

MoveableHoliday
  MoveableHoldaiYid (pk)
  HolidayId (fk)
  MonthOfYear 
  DayOfWeek
  EarliestDay
  LatestDay

If your database supports a range type you may wish to use that. Not all do, and thus the two date approach.

Why two days? Lets look at Labor day. The earliest it can be is September 1st - if Monday falls on the first. The latest that the first Monday can fall is September 6th. Monday on September 7th would mean that September 1st was a Monday and that was Labor Day.

For Memorial day, the last Monday in May (which has 31 days), the range for valid dates is the 25th through the 31st.

Some example rows:

MoveableHoliday | Labor | Memorial | Thanksgiving
  MonthOfYear   | Sep   | May      | Nov
  DayOfWeek     | Mon   | Mon      | Thurs
  EarliestDay   | 1     | 25       | 22
  LatestDay     | 6     | 31       | 28

This brings us to the "I'm not even going to calculate that" approach - it's the first Sunday after the first full moon that occurs on or after the 21st of March... and yea. If you really dug into it, you might write a stored procedure that has the calculation in it for full moon (or have that in another table - again, needing to populate that table), or you can just say "there aren't enough of these to really make a difference" and just populate it.

This is very much like the fixed holiday, except that its for one year only.

SpecificDayHoliday
  SpecificDayHolidayId (pk)
  HolidayId (fk)
  MonthOfYear
  DayOfMonth
  Year

At this point, you just populate the table however you want for however far you want.

Now, you could put the SpecificDayHoliday and the FixedHoldiay tables together, and make Year optional. However, I feel that this makes it an easier to work with each table - they each have a single type of value that goes into them. In particular, SpecificDayHoliday allows for multiple HolidayId values to be the same in the table. FixedHoliday and MoveableHoliday should have a constraint on the table that makes HolidayId unique within the table.

1
  • @user40980 Great design, but can you give a select example to find out if some specific date is a holiday?
    – Latrova
    Jun 20 '16 at 14:30
0

I have figured that it's best to store the day count which the event/holiday is on. I had a look at the Queens birthday in Australia and its every 2nd Monday of June, it doesn't move on to the next month. For most of Australia accept Western Australia that's the queen's birthday. in western Australia the queen's birth day is on every 5th Monday of September, but it sometimes falls on to October as well.

Looking at your problem I think it would be reason to store count of day of which the holiday is to fall on given a base month. If the base month is exceeded move to the next month and continue the count.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.