Hotel booking schema 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 &amp; Room.
If i set it as 1:many, i dont take into account the date for the reservation.
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 called RoomReservation which will hold multiple bookings related to ReservationHeader. FInally, I will create another table called Room which holds all data about rooms;

Customer {id (pk), Customer_Name, etc, etc, ....}
ReservationHeader {id (pk), customer_id(fk), reservation_date, etc, etc, ...}
RoomReservation {id (pk), reservation_header_id (fk), room_id (fk), check_in_date, check_out_date, etc }
Room {id (pk), room_number, room_type, etc, etc, ... }


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.