Re: Unique Keys

strider5 wrote:

>
> Kenneth Downs wrote:
> <snip>

>> I was just wondering if anybody else considers it possible, as I
>> said in my original post.

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.

>
> 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)

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)

Many other operators and functions are easy to imagine:

Min(x) := Min(a), but should this return (a,a), or a? Max(x) := Max(b)
x+1 = (b+1,b+1) (for appropriate units/resolution)

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.

-- 
Kenneth Downs
<?php $sig_block="Variable scope? What's that?";?>