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 RestaurantTimeSlot.
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.
Best Answer
I would advise you to indeed keep projections of free/taken timeslots of those restaurants. If not, the more restaurants you'll want to query, the longer your query will take.
Keeping a projection of which slots are taken and which are free will also make it a lot easier for you to fetch free slots, because the data will be in the format you'll need it to be.
As to how: you'll need some sort of stream of events that signal that a reservation was made (or cancelled, modified), for which you need individual handlers. Those handlers encapsulate the logic to update your projections.
One thing to keep in mind though is that creating/updating/deleting projection records can be a strain for NoSQL systems, if you need to retrieve documents through an other way than via Id.
P.S.: I'm assuming you can easily access reservation data of restaurants; I don't know anything about this.