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

Question

How would you simulate a hotel room reservation for guests (in PostgreSQL, if that matters)? There may be several guests in a room, but at least one.

Of course, you can associate guests with reservations using a foreign key booking_id. But how do you apply at the DBMS level that there should be at least one guest in the room?

Maybe it's just not possible?

Answer 1

Actually, if you read the question, he states that he has booked hotel rooms. This is pretty 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)

, , , OtherGuests - 0 . , .

, , n-to-n, , 1--n n-to-1 .

Answer 2

, , , BOOKING - , .

,

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

NULL, , ...

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

Answer 3

"" "". , , 0 ( )...

Answer 4

, , . ANSI ANSI :

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)));

, Postgres ( , - ).

, :

1)

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

2) :

check (boooking_id is null)

, , .. . .

Answer 5

, ? , , - , , -, .

, , , , , . Catch-22 .

Answer 6

, bookings . beds.

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

primary, , , , .

, day. , , , , . ( )

bill_id , , , -.