Database design help needed

Question

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.

• simon coleman: Code Absolute's answer gives an improved logical model, but you may also wish to generate an availability calendar for future dates (as far ahead as you are prepared to accept bookings. That might just be AvailabilityCalendar: id/date/available#/booked# which is maintained as bookings are modified. You might choose to just record available rooms, ie have AvailableRooms: room_id / date and just insert/delete rooms as required. No need to keep historic data, you can calculate that from reservations that were made/kept.

Answer 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?

• WebNovice: 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.
• Coder Absolute: Made some changes according to your comments.