Unique Keys

CREATE TABLE reservations (
  hotel_room char(4),
  date_start date,
  date_end date )

which requires a constraint that no two reservations can overlap. I believe Tony gave this one to me. If so, Tony you'll be happy to know I've got my own version of this to deal with as of last week.

It seems to me very obvious as to how to do this with a unique constraint, and I do not mean to make another table and "run out" the range into explicit unique values (though for the case of hotels I still claim that's the right way to do it).

Does anybody else consider this possible? To do so, you need Costin's advice from a thread in 2003, which I stumbled upon trying to see if this had been discussed before. He says:

> To paraphrase the principle, you should not design your schema in SQL,
> but rather design your schema as if you had the perfect DBMS in front of
> you, and after that translate it to SQL. The fact that SQL's type system
> is limited should not prevent you from using a better type system in the
> abstract (including union type), and then "encode" or translate your
> design using whatever SQL facilities you see most fit for that purpose.

If we follow Costin's advice, it becomes trivial to enforce the reservations, or any overlapping system, with a unique constraint. I will admit you may have to *really* stretch Costin's advice, but if some is good, more is probably better, no?

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