Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!news-FFM2.ecrc.net!fu-berlin.de!uni-berlin.de!not-for-mail
From: Kenneth Downs <firstinit.lastname@lastnameplusfam.net>
Newsgroups: comp.databases.theory
Subject: Re: Arbitrary Constraints
Date: Tue, 26 Oct 2004 14:21:26 -0400
Lines: 67
Message-ID: <hj4mlc.jc5.ln@mercury.downsfam.net>
References: <hbcllc.lf2.ln@mercury.downsfam.net> <1098806814.050294.83280@z14g2000cwz.googlegroups.com> <34vllc.fo4.ln@mercury.downsfam.net> <1098810796.436151.174580@c13g2000cwb.googlegroups.com>
Reply-To: sameas@email.address
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
X-Trace: news.uni-berlin.de VuLyJhie8m4SKdHIy+uaSQI7f2rsG+F+SvGH+R+DUxCdE3h6VP
X-Orig-Path: mercury.downsfam.net!nobody
User-Agent: KNode/0.7.7
Xref: dp-news.maxwell.syr.edu comp.databases.theory:27461

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