Database Design for Multiple Room Reservation: One To Many

Question

Primary Entities: Client Guest Reservation RoomAssignment

I want to implement a multiple room reservation database design. First, I want to explain first the concept:

1. The Client is the one who acquires a reservation.
2. The Client can only have 1 reservation at a time
3. The Client can reserve multiple rooms.

4. The Guest is the one who is assigned into a specific room.

   So for the Table:

   Client (client_id(PK), Name)
Guest (guest_id(PK), Name)

   Reservation (reservation_id(PK), client_id(FK), roomAss_id(FK), checkInDate);
   RoomAssignment (roomAss_id(PK), guest_id(FK), roomno(FK));
   Room(room_id(PK), roomDetails);
   

//The problem here is that I don't know how to implement a 1 to many relationship. My Reservation should handle mutliple RoomAssignment? Or my RoomAssignment will handle multiple guest_id and roomno and then I will pass 1 roomass_id into my reservation table?

Thanks I am really confused about this 1 to many relationship. I hope somebody is so kind to help instead of giving me negative points. T_T

Another Attempt:

Room(room_id(PK), roomDetails);
Client (client_id(PK), Name)
Guest (guest_id(PK), Name)
Reservation (reservation_id(PK), client_id(FK), checkInDate);
Booking(book_id(PK), reservation_id(FK), room_id(FK));
Lodging(lodge_id(PK), guest_id(FK), book_id(FK))

(Client, Room, Guest are already filled), add Reservation, add Booking, add Lodging

Is This Correct??

Answer 1

(Edit Changed my suggestion after thinking about this more. It's a deeper puzzle than I first thought.)

My preference would be to have the Reservation having a many-to-many relationship (using a bridge table) with rooms. Call this table, with ReservationID and RoomID foreign keys, something like . . . Booking. Maybe you can think of a better name. A Booking is a particular room being reserved. Then, I'd have another bridge table representing the relationship between a Guest and a Booking. You could maybe call that a Lodging. A Lodging is a particular guest being assigned to a particular Booking (a booked room).

This is a neat puzzle you have. The key is that you have several concrete things that exist on their own and are obviously necessary (reservation, room, guest), but also several other concepts, each with their own attributes, and spring from the relationship between these things. If this is going to be normalized correctly, you might end up with more tables than you guessed. Don't think of this as getting more complicated. Having the enough of the proper tables is what's going to ultimately simplify this. Here are some other suggestions:

• Spend time on the names of each table. My suggestions above are pretty good, but you can probably improve them. A booking is both a relationship between other things, but becomes a think itself, with at least one foreign key on another table.
• You should be able to describe what a record on a table represents. If you can't do that, then your tables suck. See above how I can describe what a booking and lodging are. Your design might end up being different, but as you brainstorm different tables, make sure you can describe what a record on that table actually is.
• Consider having Guest and Client being from the same table. They're both "Contacts" really. Someone might be a Guest at one point, but a Client the next month. You might ave an extra data table (one to 0-or-1) for when a Contact is the Client. You're system would only demand basic Contact information if someone is going to just act as a Guest, but more if they're acting as a Client.