How to store a store opening hours in an SQL database?

Question

I am developing an app for stores (in fact, restaurants) and need to design the PostgreSQL data structure for it.

For instance, the restaurant might have opening hours Mon-Fri from 7:30 to 17:00 and from 20:30 to 1:00 the following day.

The data to be stored for each weekday would be something like ['Monday', true, 450, 1050, 1230, 1500 ], being true === "it does open on Mondays", 450, opening time is 450 minutes past midnight (see here), i.e. at 7:30h, closes at 17:30h, reopens at 20:30h, and closes at 1 a.m. (split hours and close time after midnight are not at all unusual in my home country, Spain). Of course, I could dispense with the first two elements, 'Monday' and 'true', but they will probably make front-end development easier (e.g. see model for data input).

I have identified at least four viable options to store data in a PostgreSQL database:

1) Column 'opening_hours' in the 'restaurants' table with a jsonb datatype

[

['Monday', true, 450, 1050, 1230, 1500 ]

...

['Sunday', false, 0, 0, 0, 0 ]

]

2) One column for each hour in table 'restaurants'

I would probably omit the first two elements show above ('Monday' and 'true'). This would add 7 x 4 = 28 columns to my table:

• openMon1
• closeMon1
• openMon2
• closeMon2
• openTue1
• ...

3) New table 'opening_hours'

With a foreign key 'restaurant_id' referencing 'id' in table 'restaurants', with the same design as 2).

4) Columns by data category for all 7 weekdays

For instance, column 'open1' would be in the form of '0450-0450-0450-0450-0450-0000-0000', like here. I would thus aggregate data like in option 1), but I do not see any real advantage of the latter over the former option.

For now, option 1 is good enough for the business logic I want to implement: show opening hours in a similar way as Google does, so I don't see any reason good enough to go for 2) or 3) over 1), but of course I might miss out future possibilities a developing.

What data structure follows best practices? Is there any other option better than these ones?

Answer 1

A very flexible and well normalized way would be to store each opening period as one row in a table. An opening period can be encoded as the weekday it begins, the time of the day it begins and the duration it lasts. Each opening period is linked to a restaurant via a foreign key.

CREATE TABLE opening_period
             (restaurant integer,
              weekday integer,
              time time,
              duration interval,
              PRIMARY KEY (restaurant,
                           weekday,
                           time,
                           duration),
              FOREIGN KEY (restaurant)
                          REFERENCES restaurant
                                     (id)
                          ON DELETE CASCADE,
              CHECK (weekday >= 0
                     AND weekday < 7),
              -- prevent overlapping opening periods
              EXCLUDE USING gist (restaurant WITH =,
                                  tsrange('epoch'::timestamp + time + weekday * INTERVAL '1 days',
                                          'epoch'::timestamp + time + weekday * INTERVAL '1 days' + duration,
                                          '[)') WITH &&));