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.