Re: table design

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.