Managing holiday dates in a database

How do you store holiday date information in a database? This data will be used later to compute the number of holidays (in days) given a date period.

I was thinking of the ff design.
- 1 table containing the exact dates of all the holidays
- 1 table containing the month/day combination of recurring annual holidays

- the data in the second table could be used to generate records in the first table
- you always check the first table in the computation

What do you guys think?

Fry
Monday, January 5, 2004

You might want to use an xml object field.
Some holidays require attributes like:

* Region: Who are the participating countries

* Source: Official? Religious?

* Affected: Who's affected? Lawyers? Bus drivers?

* Calculation method: 3rd friday of August in Greg's calendars

* Calendar type: Greg's

* Official holiday name (long and short) in the language of choice

Li-fan Chen
Monday, January 5, 2004

Hello Li-fan. I don;t need to go into that detail. I would still classify by location or country but that would simply need an extra field.

Fry
Monday, January 5, 2004

Don't make things more complex than necessary. If you don't need the data to be precalculated, it would probably save time to simply calculate it every time you need it. At least, save programming time. I know I'm guilty of the same sort of overprogramming often enough, but I think that that is seldom necessary, in particular because it would be so easy to add a cache (your first table) later. If it would be hard to optimize, it'd be one thing, but this one shouldn't be.

Mike Swieton
Monday, January 5, 2004

Joe Celko has a beautiful solution for just this problem in "SQL for Smarties"

Mike
Monday, January 5, 2004

We've got a holiday table in one of our databases.. I just decided to pre-fill it with 8 years of holidays, and I'll worry about the rest later!

Maybe that won't work for your specific app, but for ours (intranet) it's fine.

Josh No-Spam Jones
Monday, January 5, 2004

I've done a lot of currency trading systems where holidays are required to determine value dates (the day the money actually moves). Typically, its a very simple table (currency code, date, text description), it stores two or three years worth of holidays and it gets loaded once a year. Lists of holidays are published by various banks and are easily found. Personally, I wouldn't add the complexity for your second table with recurring hoilidays unless your date ranges are large.

Jim S.
Monday, January 5, 2004

Just be aware that not all holidays can be pre-determined.  In Hong Kong, tomorrow could be a public holiday, bank holiday, whatever, if there is a typhoon.  we just don't know it yet.  This happens 2 or 3 times a year and our systems must allow for it.  I understand this is common in South-East Asia.

David Freeman
Monday, January 5, 2004

>Just be aware that not all holidays can be pre-determined

Exactly why I have 2 tables. The first table could contain those holidays which are not always fall on the same date -Holy Week holidays, etc. The second table would serve to store those holidays which always happen on the same day - Christmas, etc.

I have heard before that there was a good solution for this in "SQL For Smarties" - unfortunately I don't have this book.

Fry
Monday, January 5, 2004

Visit the Microsoft SQL Server newsgroup.  Joe Celko hangs around there and answers questions.  Most likely he'll copy and paste in the text from his book.  He'll also probably say something obnoxious about how tables should only have natural keys... he usually does that.

GuyIncognito
Monday, January 5, 2004

Prioritize!!  Not worth making a project out of this.  I've also just hand-coded about 10 years of holidays into a table.  What, it took 20 mins? 

Bella
Tuesday, January 6, 2004

  Recent Topics

  Fog Creek Home