MongoDB Schema for Hotel booking / room availability

Question

I’m currently building an application with a similar model to hotel booking sites.

Currently considering ways of handling availability searching.

My model looks something a little bit like the below:

Hotel
_id
name
description
star_rating
address
lat
lon

I then thought of having an availability collection something like this:

Availability
hotel_id (if availability is it's own collection)
date
room_type
room_max_occupancy
price

date would store a single day the room was available
room_type would be things like “Twin” “Double”
room_max_occupancy would be 2, 3, etc...

An example query would be:
a room in from 1st-8th June, for 2 people.

For the “search results” I’d need to return hotel.name, hotel.description, hotel.star_rating.

I’m looking for the most efficient way to store this data for the type of query listed above?

Should the availability collection be it’s own collection, or a sub-document of Hotel?

If it’s own collection, should I add the lat lon to the availability (along with hotel_id) to make searching more efficient?

Answer 1

Lets work backwards, starting from the query. What you would like is a list of available rooms for a range of dates. It is not feasible to store rooms as 'not booked' for future dates, so you'll have a collection for booking information. A query would look like:

db.booking.find({date: {$gte: from_date, $lt: to_date}});

This will perhaps get us a list of Room IDs.

Collection: booking Fields: room_id, date

The next query is one that gets us a list of Room Ids that fit the constraints. Something like:

db.rooms.find({room_type: "Double", room_max_occupancy: 2});

Barring the dates for which the rooms have been booked, we can get the availability status. If the average number of rooms per hotel is high, its not worth the space used to potentially replicate of hotel information, instead we should have Hotel Ids for each room. You must then aggregate a unique list of hotels to avoid duplicate queries

Collection: rooms Fields: room_type, room_max_occupancy, price, hotel_id

The final query is for fetching information on hotels:

db.hotels.find({id: <ID>}, {'name': 1, 'description': 1, 'star_rating': 1});

Collection: hotels Fields: name, description, star_rating, address, lat, lon

If you do need to have direct access to availability information, you will have to decide on a date limit upto which you'll allow booking (say 6 months into the future). Everyday, a script must run to add a new day of (blank) availability information to the database. You can modify the booking collection as:

Collection: booking Fields: room_id, date, is_available, hotel_id (along with fields to hold the particulars of the booking process)

Your query should now have an additional constraint:

db.booking.find({is_available: true, date: {$gte: from_date, $lt: to_date}});

You'll get access the room IDs and the hotel IDs (some redundancy to skip an additional query), from which you can get the display details for the hotel