2

I am developing a hotel reservation system have come up with two approaches to model the database (MySQL).

OPTION 1

rooms

id | name | description | total_rooms

rates

id | room_id | start_date | end_date | price

reservations

id | user_id | room_id | total_rooms | checkin_date | checkout_date | total_amount

In this method, the room price can be entered through a date range (start and end), but checking for room availability is a little more complex.

However, the number of rows in the rates table is very less.

OPTION 2

rooms

id | name | description

availability

id | room_id | date | total_rooms | booked_rooms | available_rooms | price

reservations

id | user_id | room_id | checkin_date | checkout_date | total_rooms | total_price

This method involves every individual date of each room into the availability table and the table might become very big. But I think checking for room availability and generating reports will be much easier if taking this approach.

Would like to know which method is used generally by hotel reservation systems, or if there's another different method.

1

OPTION 3:

I would prefer this option in order to design the model based on the question.

I have also split up ReservationRoom into another table, because there is a possibility that single user may be able to reserve multiple rooms, and every room may have different price on different dates.

Room
====
RoomId 
Name 
Description

RoomRate
========
RoomRateId
RoomId      /*FK to Room table*/
StartDate
EndDate
Price       /*price per room*/

Reservation
===========
ReservationId 
UserId      /*FK to user table*/
etc...

ReservationRoom
===============
ReservationId   /*FK to Reservation table*/
RoomId      /*FK to Room table*/
FromDate    /*Reservation From Date*/
ToDate      /*Reservation To Date*/
Price

I don't see any issues writing queries for the above proposed schema. Please take care of indexes and unique keys, which is beyond the scope of this question, but I thought it's worth to mention!

In order to filter the available rooms, you need to be dependent on ReservationRoom table. (Edited)

I think, this design provides you flexibility. Please highlight, if you notice any flaws in this design?

  • 1
    What is the isAvailable flag for? A room which is available today, might be booked tomorrow. Room availability depends on the dates the user is checking for, we can't simply have a blanket isAvailable flag on the room. – WebNovice Oct 20 '15 at 9:22
  • Made some changes according to your comments. – Coder Absolute Oct 20 '15 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.