Schema for opening hours MongoDB

Question

How would you store opening hours on a document, say a Library, in mongoDB, that's easily queryable with Mongoid? I've read this thread, but I'm not sure how it would be implemented with my needs.

I need to have the ability to add multiple opening and closing times per day since the Library should be able to close some hours during the day and then reopen.

I need to be able to add exceptions to these opening hours. For example; close monday on a specific date.

Please share some best practices and experiences on how one could do this the most flexible way.

Answer 1

It's difficult to provide a good solution without knowing the exact queries you'd like to run. For instance, if you're asking "what businesses are open now (5:32 PM, 5/11/2011)?" you'd want a different schema than if you were asking "when is business XYZ open next?"

In the first case, you'll want to be able to efficiently pose range queries on the current hour, minute, and day -- as well as negative queries on an exception list. Alternatively, you can handle exceptions in client code.

Last, what is the level of granularity needed? What is the smallest exception possible? Minutes? Hours? Days?

I'd post the above as a comment but I just created a user account. With additional information, I'll update this to provide an actual answer.

Answer 2

Thank you, Yeggeps, for the list of requirements.

This is a revised answer based on your requirements. Of course there is no holy grail to schemae, but I would like to motivate my answer before revision (keeping a flat structure is easy to query and maintain) with some sample data + queries based on your requirement list. I reiterate, I am not saying this is the best solution, but it is a solution which is straightforward to query and easy to maintain (imho).

Code is a little quick and dirty, apologies. The data:

[
  # library "lib1" open on wednesdays from 8:00 until 17:00
  {"lib_id" => "lib1", "type" => "hours", "opening" => 800, "closing" => 1700, "day_of_week" => 3},
  # library "lib1" open on wednesdays from 19:00 until 22:15
  {"lib_id" => "lib1", "type" => "hours", "opening" => 1900, "closing" => 2215, "day_of_week" => 3},
  {"lib_id" => "lib1", "type" => "hours", "opening" => 800, "closing" => 1700, "day_of_week" => 4},
  {"lib_id" => "lib2", "type" => "hours", "opening" => 1100, "closing" => 1700, "day_of_week" => 3},
  {"lib_id" => "lib2", "type" => "hours", "opening" => 1400, "closing" => 1700, "day_of_week" => 4},
  {"lib_id" => "lib2", "type" => "hours", "opening" => 1900, "closing" => 2100, "day_of_week" => 4},
  # library lib1 closed on wednesday december 7th 2011
  {"lib_id" => "lib1", "type" => "closed_on", "reason" => "Rearranging the shelves", "closed_date" => Time.utc(2011, 12, 8)},
  {"lib_id" => "lib2", "type" => "closed_on", "reason" => "We are closed for the holidays", "closed_date" => Time.utc(2011, 12, 7)}
].each do |schedule|
  coll.save(schedule)
end

Show opening hours and exceptional dates separately:

# List all the library id's distinctly
coll.distinct("lib_id").each do |lib_id|
  puts "\nLibrary #{lib_id} opening hours:\n--- "
  # I need to be able to show the opening hours in correlation with the Library
  # Find all the opening hour information for current library
  coll.find({"lib_id" => lib_id, "type" => "hours"}).each do |schedule|
    puts " #{Date::DAYNAMES[schedule["day_of_week"]]}s: #{schedule["opening"]} - #{schedule["closing"]}" if schedule["type"] == "hours"
  end

  # I need to show an indication if it's open or closed in correlation with the Library.
  puts "This library will be closed on: "
  # Find all the exceptions for current lib_id -- introduce a time-period restriction using Date.utc (...)
  coll.find({"lib_id" => lib_id, "type" => "closed_on"}).each do |closed|
    puts " #{closed["closed_date"].strftime("%a %B%e, %Y")}: #{closed["reason"]}"
  end
end

Which libraries are open today?

# I need to be able to query on what's open right now or some time in the future with minute granularity
# here I'll also need to be able to exclude the Librarys that has added exceptions for the given time/day
puts "---"
qtime = (Time.now.hour * 100) + Time.now.min # minute granularity
qwday = Time.now.wday  # this example only shows today
qclosed = Time.utc(Time.now.year, Time.now.mon, Time.now.mday)
# Query for all library ids which have opening times for this weekday, at this hour (+minutes)
coll.find({"opening" => {"$lte" => qtime}, "closing" => {"$gte" => qtime}, "day_of_week" => qwday}, {:fields => "lib_id"}).each do |lib|
  # Check whether current library has an exception for this specific day
  closed = coll.find_one({"lib_id" => lib["lib_id"], "closed_date" => qclosed})
  if closed
    # If an exception record was encountered, print the reason
    puts "Library #{lib["lib_id"]} is normally open right now, but is now closed: '#{closed["reason"]}'"
  else
    # Else: the library is open
    puts "Library #{lib["lib_id"]} is open right now! (#{Time.now.strftime("%a %B%e %Y, %H:%M")})"
  end
end

Produces output as follows:

Library lib1 opening hours:
--- 
 Wednesdays: 800 - 1700
 Wednesdays: 1900 - 2215
 Thursdays: 800 - 1700
This library will be closed on: 
 Thu December 8, 2011: Rearranging the shelves

Library lib2 opening hours:
--- 
 Wednesdays: 1100 - 1700
 Thursdays: 1400 - 1700
 Thursdays: 1900 - 2100
This library will be closed on: 
 Wed December 7, 2011: We are closed for the holidays
---
Library lib1 is open right now! (Wed December 7 2011, 13:12)
Library lib2 is normally open right now, but is now closed: 'We are closed for the holidays'

Admittedly, the downside to my proposed solution is that it does not capture every requirement in one query.