Modeling a 1 to 1..n relationship in the database

Question

How would you model booked hotel room to guests relationship (in PostgreSQL, if it matters)? A room can have several guests, but at least one.

Sure, one can relate guests to bookings with a foreign key booking_id. But how do you enforce on the DBMS level that a room must have at least one guest?

May be it's just impossible?

Answer 1

What about a room which has not been rented out? What you're looking for are reservations and a reservation presumably needs at least one guest on it.

I think what you're asking is whether you can guarantee that a reservation record is not added unless you have at least one guest for and you can't add a guest without a reservation. It's a bit of a Catch-22 for most DBMSs systems.

Answer 2

You could designate one of the guests as the "primary" guest and have it map to a column on the Rooms table. Of course, this is a ridiculous rule for a hotel, where it's perfectly valid to have a room with 0 guests (I very well could pay for a room and not stay there)...

Answer 3

In this context I suggest that the entity you are modeling is in fact a BOOKING - a single entity - rather than two entities of room and guest.

So the table would be something like

BOOKING
-------
booking id
room id
guest id (FK to table of guests for booking)
first date of occupancy
last date of occupancy

Where guest id is not nullable, and you have another table to hold guests per booking...

GUESTS
------
guest id
customer id (FK to customer table)

Answer 4

Actually, if you read the question, it states booked hotel rooms. This is quite easy to do as follows:

Rooms:
    room_id primary key not null
    blah
    blah

Guests:
    guest_id primary key not null
    yada
    yada

BookedRooms:
    room_id primary key foreign key (Rooms:room_id)
    primary_guest_id foreign key (Guests:guest_id)

OtherGuestsInRooms:
    room_id foreign key (BookedRooms:room_id)
    guest_id foreign key (Guests:guest_id)

That way, you can enforce a booked room having at least one guest while the OtherGuests is a 0-or-more relationship. You can't create a booked room without a guest and you can't add other guests without the booked room.

It's the same sort of logic you follow if you want an n-to-n relationship, which should be normalized to a separate table containing a 1-to-n and an n-to-1 with the two tables.

Answer 5

I think what you mean is that a room BOOKING is for at least one guest. ANSI standard SQL would allow you to express the constraint as an ASSERTION something like:

create assertion x as check
   (not exists (select * from booking b
                where not exists
                   (select * from booking_guest bg
                    where bg.booking_id = b.booking_id)));

However, I don't suppose Postgres supports that (I'm not sure any current DBMS does).

There is a way using materialized views and check constraints, but I've never seen this done in practice:

1) Create a materialised view as

select booking_id from booking b
where not exists 
   (select * from booking_guest bg 
    where bg.booking_id = b.booking_id);

2) Add a check constraint to the materialized view:

check (boooking_id is null)

This constraint will fail if ever the materialized view is not empty, i.e. if there is a booking with no associated guest. However, you would need to be careful about the performance of this approach.

Answer 6

I'd say you should create a bookings table with three primary keys. But instead of referring to bookings rooms, you can refer to a beds table.

bookings:
  bed_id: foreign_key primary
  guest_id: foreign_key primary
  day: date primary
  bill_id: foreign_key not null

beds:
  room_id: foreign_key primary

Since being the primary implies being required, and since this is the only way a guest and a room can be related, it makes sure that there cannot be a booking without a guest.

Notice that there is only one day field. This requires that you create a booking for every day a guest will stay in a room, but also ensures that nothing will be accidentally booked twice. A bed can be booked by only one customer on any given day(which is not true for rooms)

The bill_id is there so that you can refer a booking to a specific record for a bill, which can also be referenced by other things such as minibar expenses.