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.
You might want to use an xml object field.
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.
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.
Joe Celko has a beautiful solution for just this problem in "SQL for Smarties"
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!
Josh No-Spam Jones
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.
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.
>Just be aware that not all holidays can be pre-determined
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.
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?
Fog Creek Home