What is the correct way to design my application, for a Hotel reservation system.
So far i've got 3 tables
Customer
- id pk
Reservation
- id pk
- customer_id fk
- check_id_date
- check_out_date
Room
- id pk
And the premise is: a customer can have multiple reservations.
A reservation could include more than one room.
I understand the relationships between them are 1:many for Customer-Reservation.
My question is how should i model the relationship between Reservation & Room.
If i set it as 1:many, i dont take into account the date for the reservation.
Best Answer
There's no one correct way of doing it. What I would do, is to introduce another table on top of what you already have. A table that I would call
ReservationHeader
, this is the table that would hold a header information and then beneath it I would add another table calledRoomReservation
which will hold multiple bookings related toReservationHeader
. FInally, I will create another table calledRoom
which holds all data about rooms;See the diagram below for clarity. You can then add other fields and build on top of that.
You might also might try to think ahead and consider how you are going to query the tables to check for room availability. There are already plenty of good answers covering that stackexchange.