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:



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

DateTime (e.g 18/02/2014 17:00)

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.

  • 2
    Build your logic and data collections/definitions first and then start worrying how to store the information. – JeffO Feb 17 '14 at 13:19
  • @JeffO what do you mean by logic & data collections / definitions first? To perform queries, one needs to have the data-objects ready, right? – Karl Cassar Mar 5 '14 at 16:45
  • If you need a table for 6 people, do you allow a table for 4 people and a table for 2 people to be combined? Will you book 2 people into a 4 person table, if no 2 people tables are left? – Ian Apr 19 '14 at 16:25
  • How will the owner setup their reservation availability? Can they enter special days/holidays with alternate availability? These are the logic/rules of what your app will do and allow. – JeffO May 19 '14 at 17:21

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.

  • I've updated my original post, with my ideas about computing the availability of free/taken timeslots. Maybe you could take a look and comment? – Karl Cassar Feb 18 '14 at 11:08
  • @KarlCassar That looks like a nice solution, yes. The only caveat I can think of is that for some top-end restaurants, you could be looking at reservations up to a year or more in advance, so 90 days could be too little. You should have a service that runs say every night and that rolls out extra days with timeslots. Other than that, if you can subscribe to feeds that tell you when reservations are made, cancelled, etc., the code to implement this is rather simple. You pick a message from a queue, check which kind it is, resolve a handler for it and execute the handler. Easy to TDD :-) – Stefan Billiet Feb 18 '14 at 11:29
  • @KarlCassar P.S. this isn't really a forum, so if you have more questions about this, you can always ping me on Twitter (@StefanBilliet) – Stefan Billiet Feb 18 '14 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.