如何在SQL数据库中存储商店的营业时间? [英] How to store a store opening hours in an SQL database?

问题描述

我正在为商店(实际上是餐馆)开发一个应用程序，并且需要为其设计PostgreSQL数据结构.

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

例如，餐厅的营业时间可能是周一至周五的7:30至17:00，以及第二天的20:30至1:00.

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.

每个工作日要存储的数据类似于 ['Monday'，true，450，1050，1230，1500] ，为true ===它确实在星期一开放"； 450，开放时间是午夜之后的450分钟(请参见此处)，即在7:30h，在17:30时关闭:30h，在20:30h重新开放，并在凌晨1点关闭(午夜后的休息时间和关闭时间在我的祖国西班牙并不罕见).当然，我可以省去前两个元素星期一"和真"，但是它们可能会使前端开发更容易(例如，请参见

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).

我已经确定了至少四个可行的方法来将数据存储在PostgreSQL数据库中:

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

1)餐馆"表中具有jsonb数据类型的"opening_hours"列

[

['星期一'，正确，450，1050，1230，1500]

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

...

['Sunday'，false，0，0，0，0]

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

]

2)表餐厅"中每小时每小时一列

我可能会忽略上面显示的前两个元素(星期一"和真实").这会在我的表格中添加7 x 4 = 28列:

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)新表"opening_hours"

在表"restaurants"中具有引用"id"的外键"restaurant_id"，其设计与2)相同.

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

4)所有7个工作日的数据类别列

例如，列'open1'将采用'0450-0450-0450-0450-0450-0000-0000'的形式，例如

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.

就目前而言，选项1足以满足我要实现的业务逻辑:以与Google相似的方式显示营业时间，因此我看不出有足够的理由进行2)或3)1)，但是我当然会错过未来发展的可能性.

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?

推荐答案

一种非常灵活且规范化的方法是将每个开放时间段存储为表格中的一行.开放时间可以编码为开始的工作日，开始的时间和持续的时间.每个开放时间段都通过外键链接到餐厅.

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 &&));

这篇关于如何在SQL数据库中存储商店的营业时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！