Re: The wisdom of the object mentors (Was: Searching OO Associations with RDBMS Persistence Models)

Certainly. Sorry I was not explicit enough ;)

Imagine some hotel booking system...Let's say we have a ROOM_RESERVATION relvar defined as follows...(M:N) between ROOM and CLIENT (to keep things simple CLIENTS have only one attribute name identifying them uniquely) .

Table Definition
> ROOM: room_number(PK natural int)
> ROOM_RESERVATION: reservationid(PK surrogate int), reservation_client(FK int on CLIENT), reservation_roomnumber(FK int on ROOM), reservation_startdate(datetime), reservation_enddate(datetime)
> CLIENT: name

Additional information
> reservation_startdate: represents the date and time at which the reservation of a specific room starts
> reservation_enddate: represents the date and time at which the reservation of a specific room ends

The constraints ought to be implemented in which I am interested are (there may other but don't worry about them):
> reservation_startdate < reservation_enddate (the easy one)
> No room should be occupied at the same time. (I am tempted to add there should be a minimal delay of 3 hours minimum between the freeing of a ROOM and its reoccupancy - time for cleaning - but you can ignore that constraint for now)
> No external or applicative code (no stored procedure, no trigger) should be used to implement constaints. It should be defined only at CREATE TABLE time (using CHECK UNIQUE or FOREIGN KEY)

Sorry also about the symbology used...It is like pseudo code to keep it implementation independent.

Mikito Harakiri wrote:
> Cimode wrote:
> > Actually, I have an example in mind that may be interesting...How do
> > you implement a no-overlap operator constraint in SQL knowing that some
> > NULL values are within a specific. Hint: try to answer this question
> > using (CHECK, UNIQUE, FOREIGN KEY) and you will see what I am refering
> > to.?
>
> Could you please be more specific? What is the table defnition, what is
> the sample content, and what condition do you want ot assert?
Received on Fri Jun 02 2006 - 22:32:21 CEST