-2

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?

RdC1965
  • 412
  • 5
  • 8
  • If it where me I add the `days_open` table with `open_id, restaurant_id, day of week , open` . Then `opening_hrs` table with `hours_id, open_id, hr_start, hr_end` where `hr_start` and `hr_end` are time fields. Also for a day the `hr_end` would not extend past midnight, you would start at midnight for the next day to the end time in that day. – Adrian Klaver Nov 22 '20 at 20:25

1 Answers1

0

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 &&));
sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • If you store `start_time` and `end_time` the exclusion constraint gets a lot easier: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=bd8ea76c55c261120c63d6e7ac4ebdc0 –  Nov 22 '20 at 22:38
  • Thank you all! I guess sticky bit's solution would enable past midnight opening hours without to have to resort to another row starting at 00:00 the following day. – RdC1965 Nov 23 '20 at 10:19
  • @a_horse_with_no_name: Yes, that would be an alternative. It would need a second weekday column though to allow openings passing midnight to (the) next day(s). – sticky bit Nov 23 '20 at 11:09