一种非常灵活且规范化的方法是将每个开放时间段在表中存储为一行。开放时间可以编码为开始的工作日,开始的时间和持续的时间。每个开放时间段都通过外键链接到餐厅。
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 &&));