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.

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?