Intervals (Was: NIAM (was: Just one more anecdote))

mAsterdam wrote:

>
> I wonder: if we would introduce intervals as first class
> scalars would we wind up with Date, Darwen & Lorentzos'
> 6NF without the hassle? I gave it some thought.
> I think so, but I can't prove it.
>

I thought so and after working with it for awhile I now believe intervals should not be first class citizens.

What I tried was defining a column type "rdate" which was an interval of dates. Declaring column "MYDATE rdate" actually generated two columns, MYDATE_BEG and MYDATE_END. I thought it was really clever at the time, especially in the definition of unique constraints. Including a column of type rdate in a unique constraint transformed the constraint into an overlap constraint for the two columns.

Like PCB's and plastic, its big benefit was its downfall. The simple weirdness of having 1 column = 2 columns kept putting glitches everywhere.

Eventually I settled on defining intervals after the fact by having one column naming another, thereby tying them into an interval for things like unique constraints and the y > x that is inherent in all constraints, and the default of having the 2nd value take the first.

To make use of real single columns, you'd have to be able to reference sub-columns like so:

SELECT Reservation.begin FROM reservations...

...but this has two disadvantages. First, the weirdness of having 1 column = 2 columns has probably only been disguised, and second, it requires direct coding in the db server (like Postgres), which ole Ken won't be doing any time soon :)

Hmmm, actually, now that I think of it, Postgres supports user-defined domains and also supports arrays, so it may be possible to define intervals in userland without diving into the code.

-- 
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth_at_(Sec)ure(Dat)a(.com)