How to best model and search seasonal availability with Rails

Question

So I have an app where I am tracking a number of things, including flowers. Depending on the type of flower they can be available from places during certain spans of time during the year. Sometimes they can even be available during multiple spans of time (eg domestically from Mar-Jun, but can be found internationally from Sept-Dec).

What I am looking to be able to do is search for a specific date and determine all the different flowers that would be available on that date.

My idea was to have an Availability model which had a belongs_to relationship with a Flower. It would have a start_date, an end_date, and a flower_id. The problem was that dates in rails tend to be specific points in time, eg 2009-10-13. If I said a flower was available from 2009-10-01 - 2009-12-31 when 2010 came around I wouldn't see it as available.

So then I thought maybe I could have some sort of cron job that went through daily and changed the years on availability records as their end dates came up.

Maybe this is the right approach, but it feels a bit clunky. I looked through a few gems/plugins and couldn't find anything in particular that would fit my need.

Anyone have any insight?

Thanks in advance...

Answer 1

If it's for seasonal items, you could just have a (start/end)month or season field.

Answer 2

I like the Availability model idea, but I'd store just a start_month and end_month (both integers) and a notes field (for stuff like "Internationally" or "Domestically"). Then when you have a date, you just get the month field and compare to the set of ranges you have for each flower.

That might be a little compute-intensive if you have a lot of flowers. You could instead store a single Availability row for each flower, with 12 integers - one for each month - and 12 notes fields. Or if you won't have a lot of notes, you have an AvailabilityNotes model. Availability then has_many :availability_notes.

Answer 3

Given the cyclical nature of months it can be difficult perform a query to quickly select months where a given set of flowers is available.

I like the availability model, but think you should be saving just the month number (eg: October is saved as 10 in the start_month/end_month fields). Use Date::MONTHNAMES to make things human readable. (eg: Date::MONTHNAMES[10] => "October")

This allows you to easily form a named scope in Availabilities to choose what's available now.

class Flower < ActiveRecord::Base
  has_many :availabilities
  named_scope :available_today, lambda 
     month = Date.today.month
    {:include => :availabilities, :conditions => 
     ["(availabilities.start_month < availabilities.end_month AND \
      availabilities.start_month <= ? AND availabilities.end_month >= ?) \
      OR (availabilities.start_month > availabilities.end_month AND \
      availabilities.start_month >= ? AND availabilities.end_month <= ?)", 
      month, month,month,month]}

  named_scope :red, :conditions => {:colour => "red"}
end

Now you can do Flower.available_today.red to get a list of red flowers available now.

Unfortunately the named scope might populate the association with the availabilities (Flower.available_today.first.availabilities) that you don't want. I can't be sure without testing it, and I don't have the environment to do so right now. Worst case scenario you can use @flower.availabilities.select with similar arguments to the queries to prune the list.

Answer 4

First of all, is there a customer demand to get this "right?" Who wants this feature, and how do you know they want it? Have you modeled the user's workflow to determine how this actually fits into ordering behavior? Do you really take orders for flowers a year or more in advance? Is availability only based on the season -- there's no dependency on suppliers or other events that can change year-to-year? And are you anticipating that you'll always have the same inventory next year that you will this year? Or is pinning an availability date on a flower not a guarantee?

If this is just for general information purposes -- you'd like people to know what sorts of flowers they can order at what times of year -- then I wouldn't give the user a "pick a date" function at all. I'd just give them a dropdown: either for four values for seasons, or twelve for months. If they just want to know "Is it likely I can get lilies for my mom's birthday?" that's fully sufficient.

And you could model it very simply, with four or twelve boolean values in your model, and four or twelve checkboxes on your flower create/edit form. (Yeah, I know, it's "purer" to do a :has_many association, but unnecessary; the number of months in the year isn't going to change.)