Looking for best way to retrieve business hours from database

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: 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!

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:

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.

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.

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