Mr. Brian Gale wrote:
I second the "nonwork_days" table, but I would make sure to design the reason code table well.
You would want to make sure that the reason code table has a reason code identifier, a reason code description, and a reason code location. The location may need to be broken up into multiple columns to handle country, state/province, and potentially city. This will allow you to quickly and easily account for holidays in the USA vs CANADA vs JAPAN vs CHINA vs etc.
Excellent point. Actually country and client_id are in our nonwork_days table, and others might need similar distinguishers. But extraneous things like a fiscal year, standard character representation, etc., should not be in this table.
You can denormalize and have the main calendars table indicate non-work days also by using a join to the nonwork_days table to update the calendars table. The nonwork_days table must control what is a work day or not.
SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."