Re: Arbitrary Constraints

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Tue, 26 Oct 2004 14:21:26 -0400
Message-ID: <hj4mlc.jc5.ln_at_mercury.downsfam.net>

Tony Andrews wrote:

> Kenneth Downs wrote:

>> As for scheduling, your experience agrees with mine, that this is a
>> different kind of problem from the other two.  Do you have in the

> schedule

>> table a unique key on room + date?  Do you have the concept of a

> fixed time

>> period, such as March 25th means March 25th after 3:00pm until March

> 26th

>> before 11:00am?  If so, how can anybody overlap if you are unique on

> room +

>> date?

>
> You assume that a 14-day reservation will consist of 14 one-day rows,
> in which case I agree there can be no overlap. I was thinking of a
> (common) table structure like:

I assumed nothing. I *concluded* that in order to maintain a reservation system that adhered to the KISS principle, we would first seek a unique natural key that reflected reality. One paying party per room per night just so happens to fit. Hey, first try!

From here, the UI would take things like start date and end date and query the database, something like:

SELECT room_id,view,room_class FROM rooms   WHERE room_id NOT IN
   (SELECT room_id FROM reservations
    WHERE date between Begin_date AND end_Date)

Friendly Clerk: Mr. Jones, we have several rooms available for that two week period, do you prefer beach or bayside?

Then you get fancier from there, coming up with more queries if the first one was empty.

Being satisfied that the most basic operations are possible with this key, it is farthest our mind to *complicate* the database by getting smart. This is a disservice to the hotel owner, the users, and the poor tired folks trying to get reservations.

>
> create table reservation (room#, from_date, to_date, ..., unique
> (room#, from_date));
>
> Now we need a constraint that ensures something like:
>
> not exists
> ( select null
> from reservation r1, reservation r2
> where r1.room# = r2.room#
> and r1.from_date < r2.from_date -- to ensure it's not the SAME row
> and r1.to_date >= r2.from_date
> )
>

This logic belongs in the UI.

As Laconic2 lately reminded us, it has been said by a wiser man than I that you should not build your house on sand. The database is foundation. Simple is rock, complex where simple would suffice is sand.

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me

Received on Tue Oct 26 2004 - 20:21:26 CEST