Thread: table design

table design

From

"hostel Nate "

Date:

30 April 2007, 05:10:44

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.

Re: table design

From

"Phillip Smith"

Date:

30 April 2007, 05:18:45

Can you clarify the differences between a “Booking” and “Reservation”?

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

*******************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

Re: table design

From

"hostel Nate "

Date:

30 April 2007, 06:57:44

A reservation would be when someone is going to come but has yet not. Paid guests would be people that are already here and paid. The booking table would be either paid guests or reservations. It says that the room is taken by either a reservation or a paid guest.

-- paid guests
create table room_stay (
id             int serial primary key,
guest_id   int not null references guest(id),
room_id    int not null references room(id),
paid_to     date not null,
paid_from date not null,
amount     decimal not null,
... -- stuff specific to room_stay
);

-- reservations
create table reservations (
id             int serial primary key,
guest_id   int not null references guest(id),
room_id    int not null references room(id),
arrives      date not null,
leaves      date not null,
amount_to_be_paid decimal not null,
... -- stuff specific to reservations
);

-- alternative bookings table takes the redundant fields
create table bookings(
id                    int serial primary key,
guest_id          int not null references guest(id),
room_id           int not null references room(id),
booked_from    date not null,
booked_to        date not null,
reservation_id    int references reservation(id),
room_stay_id    int references room_stay(id),
check ( not(reservation_id is null and room_stay_id is null)
);

Using the bookings table would be trickier since inserts have to go through a trigger procedure to check that is not over-booked. You would have to make a dummy table to do the inserts since the data is spread over two tables. The main fields are really identical and deal with time and certainty of the action happening.

Combining the tables I get

-- denormalized?
create table all_stays (
id                    int serial primary key,
-- new field to differentiate reservations and paid guests
booking_status_id int references booking_status(id),
guest_id               int not null references guest(id),
room_id                int not null references room(id),
booked_from         date not null,
booked_to             date not null,
-- reservation specific stuff
...
--- paid guest specific stuff
...
);

This make the insert trigger a lot simpler but combines reservation only fields and paid guest only fields.

Re: table design

From

"Phillip Smith"

Date:

30 April 2007, 07:16:50

Understand now… If the Bookings table is just a combination of Reservations and Paid Guests, then could you use a View?

If not, maybe Table Inheritance would be something you’re after. I’m not up to speed on that aspect of things. Someone smarter than me may be able to help, or the docs.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of hostel Nate
Sent: Monday, 30 April 2007 16:58
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] table design

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

Re: table design

From

"hostel Nate "

Date:

30 April 2007, 08:11:28

I think I see what the problem is now. My first attempt was with Reservation and Paid Guest tables only and combine the queries to check all bookings. But, I was trying to use LEFT JOIN for the view. The inheritance example shows using UNION for a similar problem, which I think will take away my headaches.

Thanks.