Re: table design

Can you clarify the differences between a "Booking" and "Reservation"?

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of hostel Nate
Sent: Monday, 30 April 2007 15:05
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] table design

HI,

I am designing a reservation system booking system. I want to put as much
logic into the database as possible so it is independent from the
application. One of the constraints is that a room can not be overbooked. I
have tried some different ideas of how to do it.

The idea is that you have rooms that can be either reserved or can be booked
in with a guest. But, the rule is that no room can be booked by either a
reservation or a paid booking more than once.

One idea, break the bookings into reservations and paid guests. The issues I
had with that idea is that is was difficult to join the views and the
double-book functions were duplicated for two tables.

Two, add a booking table which must have either a reservation dependency id
or a booking dependency id. This seems to me the best way to handle the data
because it seems that bookings should be a table to themselves. But, the
triggers to enforce it get much more complex in this scheme.

Three, denormalize the tables into one. The triggers become very simple but
what the data means becomes convoluted and a new field has to be added to
denote what kind of booking it is. The field paid_from, paid_to is somewhat
of a lie if it is reservation.

Any thoughts one what would be the 'best' way to handle this situation would
be appreciated.

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments