Looking for best way to retrieve business hours from database

Question

I'm using Ruby on Rails and I'm storing business hours like this:

CREATE TABLE "business_hours" (
 "id" integer NOT NULL PRIMARY KEY,
 "business_id" integer NOT NULL FOREIGN KEY REFERENCES "businesses",
 "day" integer NOT NULL,
 "open_time" time,
 "close_time" time)

(which came from the thread at: http://stackoverflow.com/questions/1036603/storing-business-hours-in-a-database )

Now I want to pull the hours out for each day of the week and display them, and I'm trying to find the best (or at least a good) way.

Should I just have a helper method that loops through getting the days (from 0..6) for a given business_id and assign it to a variable for the associated day? I feel like there must be a better way -- with an array, or something, but it's hurting my head thinking about it, because I also have a form of 'select's where any of the hours for a given business can be updated at once.

Thanks for any guidance!

Answer 1

Since the number of days in a week really is fixed, you can join the table 6 times (plus the original) and do a query for a single row. I'd probably just do a single query and loop through the rows though.

Answer 2

Use the enum column plugin to declare the day field as a enum field.

class BusinessHours < ActiveRecord::Migration
  def self.up
    create_table :business_hours do |t|
      t.integer   :business_id, :null => false
      t.enum      :day, :limit =>[:sun, :mon, :tue, :wed, :thu, :fri, :sat], :nill => false
      t.time      :open_time, :null => false
      t.time      :close_time, :null => false
    end
  end

  def self.down
    drop_table :business_hours
  end
end

Now when you do find on the BusinessHour model you will get the day as a string.

   b = BusinessHour.find_by_business_id(2).first 
   p b.day.to_s.camelize #prints Sun/Mon/Tue etc.

You can use the enum_select and enum_radio form helpers to create list box/radio button group for the enum group:

Answer 3

Have you considered serializing the business hours? Using serialization you are essentially storing objects in the database.

class BusinessHour < ActiveRecord::Base
  serialize :hours
  ...
end

BusinessHour.create :business => @business, :hours => 
  {:mon => [mon_start_time, mon_end_time], :wed => [wed_start_time, wed_end_time],
   ...}

Personally I would go with the bitwise approach described in linked question. All you really need to do to make it work is write new accessor methods.

Answer 4

It would be easier to find the business and use the associations to retrieve the business_hours rows.

Try this in your view

<% @business.business_hours.each do |hrs| %>
  <%= hrs.day_name %>: Open-<%= hrs.open_time %> Close-<%= hrs.close_time %>
<%- end -%>

In your business_hour.rb model file, create a default scope to make sure the days are always listed in order. You can also create the day_name method to make it easier to display the day.

default_scope :order => 'day ASC'

def day_name
  case self.day
    when 0 then "Sun"
    when 1 then "Mon"
    ...
  end
end