MongoDB hours of operation

Question

How could you store hours of work in a document, say, in a library, in mongoDB, which is easily queried using Mongoid? I read this thread, but I'm not sure how it will be implemented with my needs.

I need to be able to add several openings and closures per day, since the Library should be able to close several hours during the day, and then open it again.

I need to be able to add exceptions during these hours of work. For instance; close Monday at a specific date.

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

Answer 1

Thanks, Yeggeps, for the list of requirements.

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

The code is a little quick and dirty, apologies. 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:

 # List all the library id 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 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 

What libraries are open today?

 # I need to be able to query on what 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 

It outputs 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 disadvantage of my proposed solution is that it does not take into account each requirement in a single request.

Answer 2

It is difficult to provide a good solution without knowing the exact queries you want to run. For example, if you ask: "What enterprises are open now (17:32, 5/11/2011)?" you need a different scheme than if you asked: "When does the XYZ business open further?"

In the first case, you want to be able to effectively set range requests for the current hour, minute, and day, as well as negative requests in the exclusion list. In addition, you can handle exceptions in client code.

Finally, what is the level of detail? What is the smallest exception possible? Minutes? Clock? Days?

I would post this as a comment, but I just created a user account. With more information, I will update this to give a real answer.