Impossible Database Design

SQL Apprentice Question
I was just wondering if it is possible to create a database design
where you can have


1. Infinite repeating events like "every year, every last monday,..."


2. The possibility to test whether events overlap other events


I would say it is not possible since I would need to run an infinite
query to catch a singular event..
Any ideas?
What would be the 'nearest' approach?

Celko Answers
>> you can have 1. Infinite repeating events like "every year, every last monday,..."
<<

Create a view which uses the CURRENT_DATE to get the year and to form
a window of (-x, +x) years around it. I use this trick for a table of
5 second intervals in a day.



>> 2. The possibility to test whether events overlap other events <<


The usual way to do that is see if a calendar date falls between the
start and end dates of more than one event. But you already know that
a Monday event will not overlap a Tuesday event, July event will not
overlap a June event, etc.

>> My conclusion would be that probably the best way would be to simply not support queries for predicting overlaps for dates without ending point.. <<


You can use a NULL end_date for an "eternity marker" then "COALESCE
(end_date, CURRENT_DATE)" is a VIEW or queries for the current state of
affairs. For longer term, use something like "COALESCE (end_date,
(SELECT MAX(end_date FROM Calendar))".

You do not have to generate all the dates (which can only go to
9999-12-31 in ISO Standards), but just a subset of them as needed. The
days-of-the week cycle is every 19 years (i Think) so you can use that
fact to set your window.


>> Can you think of any better evidence that Joe is a self-aggrandizing ignorant who wouldn't recognize logic if it bit him on the ass? <<


I am self-aggrandizing when I mention someone else's book? Wow!
Great logic.

Check the literature; Chronons are out of fashion and have been for
some time. For example, TSQL projects have had about 18-20 people
working on them, all with a decade or two in temporal data and they
reject that model. Jan Hidders is more to date of this than I am, if
you want current info.


I do not need to materialize Aleph null or Aleph one rows in a table
if I can test any requested value to see if it is an element of the
set. Remember basic set theory? I can either enumerate a (finite) set
or I can give a membership rule (infinite sets). Then we argue about
what rules the rules must follow. Dedekind cuts at pi and all that
jazz.


>> Java's Date class (irony?) uses a 64 bit long as milliseconds, giving it millisecond resolution, and the ability to represent dates crazy-far into the future <<


The old FIPS-127 specs said that SQL had to go out to at least 5
decimal seconds, but önly" to 9999-12-31; do java programmers think
their code will still be in use the year "crazy-far"? :)

Yes, there is some complexity in doing it in SQL, but I can copy the
code and feel pretty sure about it. Your code will always look better
in a make-believe language that you do ot have to implement. But I
find some of Date's code to be problematic. Various arrangements of
PACK() and UNPACK() produce different representations of the same
facts, which he considers a problem in SQL, but not in his language.

I can live with the assumption that time has a starting point -- some
temporal logic uses that model because time moves in one direction.
But I cannot see time with a pre-determined end point.


The one that really got me, however, was some charts in the back of the
book where one axis is parts (P1, P2, etc.). He puts them into
intervals, just the way he does Chronons. Parts are discrete and
keyed by a nominal scale, and are not continous or dense. If you stop
and look at it, this is a multi-value DBMS model, not a relational one.


And time is a bitch to work with by its nature-- remember the old kids
math puzzle Ïf a hen and half can lay an egg and half in a day and a
half, then how long does it take for ..?"



>> Date's approach seems completely reasonable to me. After all, we are dealing with computers, right? The best we can hope for are acceptable


representations of continuous systems. I mean, is there really a need
to handle a time interval as an infinite number of instants? <<

No, not as an "infinite number of instants", but as a continuum, which
is very different. A continuum has no points, so everything is an
interval. This is what explains Zeno's paradoxes. This is a model,
not an implementation. What we should have done in SQL was require the
(start,end) model instead of points in time either directly or by
implication