Re: Unique Keys

Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net> wrote:

[snip]

>Yup, that's it. You need a type which is basically an ordered pair of any
>other type, which in this case is a date.
>
>The type has built in to it the fact that given any x = (a,b), a<=b. This
>type of validation is similar to the case where the type engine disallows
>February 35th.

     Fine.

>> candidate key would be (hotel_room, span)
>> and additional constraint would be declared as
>>
>> select 0 from reservations r1 NATURAL JOIN reservations r2
>> where OVERLAPS( r2.SPAN , r1.SPAN) = {}
>> which is IMHO better "readable" than Mikito's equivalent
>
>I would suggest it is more useful to define the equality operator as such:
>
>where x = (a,b)
>and y = (c,d)
>
>x = y iff (a <= c <= b) OR (a <= d <= b)

     This is simply overlap, not necessarily equality.

>It is the equality operator definition that makes it a declarative unique
>constraint exactly the same as any other, and also makes possible simple
>foreign keys. Now we have:
>
>CREATE TABLE reservations (
> hotel_room char(4),
> span DATEINTERVAL )
> PRIMARY KEY (hotel_room,span)
>
>And the super-nifty:
>
>CREATE TABLE Something_Else (
> ....
> hotel_room char(4),
> span DATEINTERVAL )
> FOREIGN KEY (hotel_room,span)
> REFERENCES reservations (hotel_room,span)
>
>It is also worthwhile to allow implicit conversions between any range type
>and an atomic value of the same underlying type, so that
>
>where x = (a,b)
>and y = c
>
>x = y iif (a <= c <= b)

     Yuck again.  I can see
          y in x

>Many other operators and functions are easy to imagine:
>
>Min(x) := Min(a), but should this return (a,a), or a?

     The minimum is a date, not a date range.

>Max(x) := Max(b)

     Ditto.

>x+1 = (b+1,b+1) (for appropriate units/resolution)

     Potentially rather confusing.

>Now you can find the next open period for a room with:
>
>SELECT max(span)+1 as NEXT_OPEN_DAY FROM reservations...
>
>Since the databases do not support this, I support it as a type in my data
>dictionary, and let the db builder do the relevant gymnastics to make it
>all work. From the database analyst's point of view, it is an atomic type
>that can be joined, max'd, etc.

     I think your unequal definition of the equality operator would lead to trouble.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:

     I have preferences.
     You have biases.
     He/She has prejudices.