Calendar Table

>> Hey Celko, didn't you once say you were going to write some stuff on
auxiliary tables? Haven't seen that yet... :) <<
I am banging away on the third edition of SQL FOR SMARTIES right now,
with a new and improved auxiliary tables chapter in it.
Minor design points:
1) do not use flags for each date type, but invent a single encoding
for holidays, workdays, etc. Leave room in the code for expansion, say
you have 100's for holidays, 110's for civil holdiays, 120's for
religious holidays, etc.
2) I prefer a BETWEEN predicate over comparisons (cal_date BETWEEN
order_complete_date AND order_ship_date), becuase it reads better for
someone maintaining the code.
But it also means I have to have constraints on the columns to adjust
the time part of the DATETIME columns to midnight today or a
microsecond before the next day.On 22 Jan 2005 11:48:16 -0800, --CELKO-- wrote:

>auxiliary tables? Haven't seen that yet... :) <<
>I am banging away on the third edition of SQL FOR SMARTIES right now,
>with a new and improved auxiliary tables chapter in it.
>Minor design points:
>1) do not use flags for each date type, but invent a single encoding
>for holidays, workdays, etc. Leave room in the code for expansion, say
>you have 100's for holidays, 110's for civil holdiays, 120's for
>religious holidays, etc.
Hi Joe,
This doesn't sound like a design I'd be too happy with.
First, the difference between civil holidays and religious holidays is
often not important at all. People shouldn't clutter their databases with
unimportant information.
Second, if this distinction is important, it is still a seperate attribute
(holiday_type). Trying to encode this distinct attribute into a numeric
range is a vioaltion of 1NF.
Third, using a meaningless, unverifiable numeric code for holidays is not
needed at all. At this point, I'm tempted to ask if you also believe that
God put a 17-letter hebrew number to everything in Creation. What's wrong
with calling St.Patrick's Day St. Patrick's Day, New Years Day New Years
Day and Christmas Christmas?

>2) I prefer a BETWEEN predicate over comparisons (cal_date BETWEEN
>order_complete_date AND order_ship_date), becuase it reads better for
>someone maintaining the code.
>But it also means I have to have constraints on the columns to adjust
>the time part of the DATETIME columns to midnight today or a
>microsecond before the next day.
But since the precision of date and time datatypes are not specified in
SQL-92 (at least not that I'm aware of), the microsecond before the next
day might get rounded up - and then your BETWEEN won't work. You'd have to
adjust the contents of the time table whenever you port your code.
Would you really throw away portability for "better reading" (which is
very subjective to boot)?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 22 Jan 2005 14:01:55 -0800, --CELKO-- wrote:

> While
> in most cases, civil versus religious is not really needed unless you
> are being sued for discrimination, I can imagine wanting to know if a
> work-day called off for bad weather.
I can imagine running a db for a diverse organization, where some
departments get all holidays, others only get civil. Or where when an
employee takes off day X, you credit it to a holiday category if it is a
religious holiday even if it is not a standard company holiday (a variation
on your discrimination idea I guess). Anyway, I suppose the point could be
argued, but without business requirements there's not much point in
arguing.