TransWikia.com

Hotel booking schema

Database Administrators Asked by Rakoun on December 20, 2020

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.

One 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 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.

hotel reservation schema

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.

Answered by Phemelo Khetho on December 20, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP