I am looking into finding an efficient way which can scale up to thousands of restaurants, for doing a reservation search. Ideally, it would be efficient to answer queries like find a restaurant which has availability for 6 people at 19:00. Time slots are based on 15-minute interval, e.g 19:00, 19:15, 19:30, ...
I am using a document database (RavenDB), but the issue is more conceptual and I don't think it is dependent on the underlying datasource.
Each restaurant can have a different amount of capacity, for example restaurant A might fit 40 people, while restaurant B might fit 60.
My first instinct was to have a
Restaurant collection, and a
Reservations collection. However, I cannot think of a way how an efficient query can be made on the restaurant/reservations collections to actually get a result for a restaurant only if it has enough space at that particular time for a booking. Sample data objects below:
Restaurant ---------- Id Name Capacity Reservation ----------- Id DateTime RestaurantId NoOfPeople
The other option that came in mind was to actually keep a track of each day and all the timeslots in a day (each 15 min interval). Then, with each booking, each respective timeslot is updated. Hence, the query would be a simple search on the timeslots. This would work (haven't tried it yet), but it involves more 'maintenance'. Is this the best way to go for such a problem, or is there maybe a side of it which I'm not seeing?
Computing availability on bookings
My idea for the second option, to compute availability for bookings is that I would add another data object, as below
RestaurantTimeSlot ------------------ RestaurantId DateTime (e.g 18/02/2014 17:00) AvailableBookings MaxBookings
A background task would create new time slots for restaurants for subsequent days, example creating up to 90 days in advance so that users can book a maximum of 90 days from today. Each day would have all the different timeslots, example 00:00, 00:15, 00:30, so on so forth. Initially, the
RestaurantTimeSlot would be set as
AvailableBookings = 0,
MaxBookings = maximum bookings of that restaurant (e.g 60). The reason for
MaxBookings is because a restaurant availability might be based on time, and in 2012 it might have availability for 60, while in 2013 it might expand to 80. Then, when a booking for say 4 people is made at 18/02/2014 17:00, that respective timeslot's
AvailableBookings would be added by 4. Then, when a search needs to be done, one would search easily in
Does this make sense? My issue that having to constantly create these new data objects so that they are available for search, and keeping them in sync when a new booking is made. This looks like it can lead to the system being more error-prone.