Question 115079 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – What's the best approach to create opening hours model for a store

I would like to store an display OpeningHours for stores like this slots example:

mon = 9h00 => 12h00, 14h30 => 20H00

tue = 9H00 => 20H00

we = 14h15 => 20h00

thu = closed

I need to use it for OpeningOursSpecifications from Schema.org

Tags:

3

Answers


  1. 0

    Interesting question…

    I would create models as follows:

    class Store
  has_many :day_hours
end

class DayHour
  #columns day, am_open, am_closed, pm_open, pm_closed, store_id

  belongs_to :store

  enum day: Date::ABBR_DAYNAMES
end

    I definitely would not use jsonb for the hours, why not use columns? In my experience there are always downsides to jsonb that come up later (searching records, changing values, sorting… etc)

    The enum column is optional, it gives you some nice methods to use.

    Login or Signup to reply.
  2. 0

    Rails does not support JSONB well, and this is a perfect fit for a conventional relation.

    However, do not create a table with 28 columns. If you find yourself making lists as columns, you probably have a design problem. Instead, make a table with one row for each day-of-week/open/close time.

    create_table :store_hours do |t|
  t.references :store, null: false

  # Store the day of week using an integer and then
  # Use ActiveRecord::Enum to turn it into strings.
  t.day_of_week :integer, null: false

  # Use the Time and Interval types so you can do time math easily.
  t.open_at :time, null: false
  t.close_at :time, null: false
end

class StoreHour < ApplicationRecord do
  belongs_to :store

  enum :day_of_week, monday: 0, tuesday: 1, wednesday: 2, thursday: 3, friday: 4, saturday: 5, sunday: 6

  # Check if new hours overlap with exisiting hours.
  validates :open_at,
    scope: [:store_id, :day_of_week]
    conditions: ->(store_hour) {
      where(
        "open_at <= ? and close_at => ?",
        store_hour.close_at, store_hour.open_at
      ) 
    }
end

class Store < ApplicationRecord do
  # A store has many store hours. They will be deleted when the store is.
  # Deleting is ok here, not destroy, because there's nothing special to do
  # when the StoreHours are deleted.
  has_many :store_hours, dependent: :delete
end

    This lets a store have as complicated an open/close time as they want.

    See Building Complex Forms for how to write the controller and view to add, edit, and remove StoreHours.

    Some comments:

    • See Active Record Associations for how dependent works and how to work with the associated StoreHours.
    • I’ve added a unique validation to ensure a store does not have any overlapping opening/closing times. See validates_uniqueness_of and Active Record Validations.
    • The day of week is stored as an integer. The class uses ActiveRecord::Enum to map between integers and strings. This lets you still refer to values as "monday" but it is stored as a number to save space and time and to prevent typos. I’ve made the mapping between strings and numbers explicit in case more days need to be added (for example: weekends, holidays).
    • With this model, stores must close before midnight.
    • If you instead store when a store opens and how long its opened as an interval, stores to be open after midnight. For example, open_at: 12pm, open_for: 13 hours means from noon to 1am the next day. This is more complicated, but also more flexible.
    • Alternatively, you can store open_at/closed_at as timestamps, but always use the same date: 2000-01-01. A store that is closed after midnight would be 2000-01-02.
    • The Postgres time type is mapped to a Ruby Time object, but with the date 2000-01-01.
    • We use has_many :store_hours, dependent: :delete to ensure when a store is destroyed its StoreHours are also deleted.
    • Here is a demonstration of the time overlap validation logic.
    Login or Signup to reply.
  3. 0

    For relational database use 5 columns table:

    store_id
dayofweek    (enumerated type: 1-Sun..7-Sat)
period_begin (time type, including)
period_end   (time type, excluding)
period_type  (enumerated type: 1-opened, 2-whatever_state1, ...)

    Treat the store as closed by default and store only records with other types (like opened).

    1 2 9:30  12:00 1 (opened)
1 2 14:00 18:00 1 (opened)
1 3 9:00  20:00 1 (opened)
1 4 0:00  24:00 2 (whatever_state1)
1 5 0:00  24:00 3 (whatever_state2)

    If stores and users are in different time zones you may store period_begin/end in UTC and calculate and show local time and day of a week accordingly user’s timezone.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search