Firestore data modeling for a booking app for easy availability queries

Question

I wanted to ask for an advice on data structuring best practices for Cloud Firestore for the following scenario.

There's a booking/appointment app. Hotels rent out rooms. Each hotel has multiple rooms. Clients can search the rooms of all hotels by availability on specific days.

What is the best way to structure the availability data in Firestore so I could create a view of all available rooms throughout all hotels.

I thought of creating a separate collections where I would put all the reservations referencing the room ID and date of the reservation. However, it seems like I won't be able to search for available slots this way since Firestore can't perform 'not equals' queries.

So I thought I would create an array field for each room containing all the available dates as timestamps. This creates another problem. Even though I can use 'array_contains' query, users can't check availability for more than one day this way since 'array_contains' can only be used once per query.

What would be the most efficient way to structure the data in this case?

Thank you!

Answer 1

What is the best way to structure the availability data in Firestore so I could create a view of all available rooms throughout all hotels.

A possible database structure that can help you achieve what you want, might be this:

Firestore-root
    |
    --- hotels (collection)
    |    |
    |    --- hotelId (document)
    |          |
    |          --- //Hotel properties
    |
    |
    --- rooms (collection)
    |    |
    |    --- hotelId (document)
    |         |
    |         --- hotelRooms (collection)
    |               |
    |               --- roomId (document)
    |                     |
    |                     --- available: true
    |                     |
    |                     --- hotel: "hotelId"
    |                     |
    |                     --- //Other room properties
    |
    |
    --- availabeRooms (collection)
          |
          --- roomId (document)
                |
                --- available: true
                |
                --- hotel: "hotelId"
                |
                --- //Other room properties

As you can probably see, I have duplicate some data in order to achieve what you want. This practice is called denormalization and is a common practice when it comes to Firebase. For a better understanding, I recommend you see this video, Denormalization is normal with the Firebase Database. It's for Firebase realtime database but same principles apply to Cloud Firestore.

Also, when you are duplicating data, there is one thing that need to keep in mind. In the same way you are adding data, you need to maintain it. With other words, if you want to update/detele an item, you need to do it in every place that it exists.

Using this database schema, you can simply query the database to get all available rooms from all hotels by attaching a listener on availabeRooms reference and get all room objects. If you want to get the details of the hotel from which a particular room is apart, you need to make an extra call to get the hotel details. I have stored within the room object, only a reference of the hotel object which is as you can see, the hotelId. You can also store the entire hotel object but before taking a decision, I recommend you to be aware of some details that can be found in my answer from this post.

Furthermore, if a room becomes unavailable, simply change the value of the available property that exist under rooms -> hotelId -> hotelRooms -> roomId to false and remove the corresponding room from the availabeRooms collection. That's it!

P.S. If you want to get all the available rooms within a single hotel, just attach a listener on rooms -> hotelId -> hotelRooms and get all available rooms using a query that should look like this:

Query query = db.collection("rooms").document(hotelId)
        .collection("hotelRooms").whereEqualTo("available", true);

Edit:

According to your comment regarding the date of the reservation, you should create a calendar of reservations for each room separately. Then just simply create a function, in Cloud Function that can be triggered using a cron job. This function can help you check the availability for each room daily. If the room is available, set the available to true otherwise, set the property to false and remove the room from the availabeRooms collection.