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

database, postgresql

Solution

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.

Problem

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?

Original source