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.