database 在数据库中存储营业时间

Storing Business Hours in a Database

提问by user128000

I'm currently trying to work out the best way to store a business' hours of operation in a database.

我目前正在尝试找出将企业的营业时间存储在数据库中的最佳方式。

For example:

例如：

Business A has the following hours of operation

企业 A 的营业时间如下

• Monday: 9am - 5pm
• Tuesday: 9am - 5pm
• Wednesday: 9am - 5pm
• Thursday: 9am - 5pm
• Friday: 9am - 5pm
• Saturday: 9am - 12 Midday
• Sunday: Closed

• 星期一：上午 9 点至下午 5 点
• 周二：上午 9 点至下午 5 点
• 星期三：上午 9 点至下午 5 点
• 星期四：上午 9 点至下午 5 点
• 周五：上午 9 点至下午 5 点
• 星期六：上午 9 点至中午 12 点
• 星期日：休息

Currently I'm have a data model similar to the following

目前我有一个类似于以下的数据模型

CREATE TABLE "business_hours" (
    "id" integer NOT NULL PRIMARY KEY,
    "day" varchar(16) NOT NULL,
    "open_time" time,
    "close_time" time
)

where the "day" is restricted to a choice of the 7 days of the week in code (through the ORM). To test if a business is closed on a certain day it checks if the open_time and close_time are NULL. It is related to the business through a intermediate table (Many To Many Relationship).

其中“day”仅限于在代码中选择一周中的 7 天（通过 ORM）。为了测试一家企业是否在某一天关闭，它会检查 open_time 和 close_time 是否为 NULL。它通过一个中间表（Many To Many Relationship）与业务相关联。

Does any one have any suggestions for this database scheme? Something about it doesn't seem right to me.

有人对这个数据库方案有什么建议吗？关于它的某些东西对我来说似乎不正确。

采纳答案by Frank Krueger

Overall, I see nothing wrong with this. Except...

总的来说，我认为这没有任何问题。除了...

1. I would store the day of week as an integer using whatever numbering system your native programming language uses (in its libraries). This will decrease the size of the database and remove string comparisons from your code.

2. I would probably put the foreign key to the business table right here in this table. That way you won't need a link table.

1. 我会使用您的本地编程语言使用的任何编号系统（在其库中）将星期几存储为整数。这将减小数据库的大小并从代码中删除字符串比较。

2. 我可能会将外键放在这张表中的业务表中。这样你就不需要链接表了。

So I guess I would do:

所以我想我会这样做：

CREATE TABLE "business_hours" (
     "id" integer NOT NULL PRIMARY KEY,
     "business_id" integer NOT NULL FOREIGN KEY REFERENCES "businesses",
     "day" integer NOT NULL,
     "open_time" time,
     "close_time" time
)

In my business logic, I would enforce a constraint that every "business" has at least7 "business hours". (At leastbecause Jon Skeet is right, you might want holiday hours.) Though you may want to relax this constraint by simply leaving off "business hours" for days that the business is closed.

在我的业务逻辑中，我会强制执行一个约束，即每个“业务”至少有7 个“营业时间”。（至少因为 Jon Skeet 是对的，您可能需要假期时间。）尽管您可能希望通过简单地将“营业时间”留在公司关闭的日子来放松这一限制。

回答by davidsheldon

One situation that isn't covered by this schema is several opening periods in a day. For example, the local pub is open 12:00-14:30 and 17:00-23:00.

此模式未涵盖的一种情况是一天中的多个开放时段。例如，当地酒吧的营业时间为 12:00-14:30 和 17:00-23:00。

Maybe a theatre box office is open for a matinee and an evening performance.

也许剧院售票处为日场和晚间表演开放。

At that point you need to decide if you can have several entries for the same day, or if you need to represent different hours in the same row.

此时，您需要决定是否可以在同一天有多个条目，或者是否需要在同一行中表示不同的时间。

What about opening times that cross midnight. Say a bar is open 19:00-02:00. You couldn't just compare the opening and closing times with the time you want to test.

跨越午夜的开放时间呢？假设酒吧在 19:00-02:00 开放。您不能只将打开和关闭时间与要测试的时间进行比较。

回答by CTS_AE

I have learned that if you want to have google data markup recognize your data you should follow these guidelines:

我了解到，如果您想让谷歌数据标记识别您的数据，您应该遵循以下准则：

https://schema.org/openingHours

https://schema.org/openingHours

http://schema.org/OpeningHoursSpecificationContains "valid dates", which is very useful for some businesses.

http://schema.org/OpeningHoursSpecification包含“有效日期”，这对某些业务非常有用。

https://schema.org/docs/search_results.html#q=hours

https://schema.org/docs/search_results.html#q=hours

You should be fine without a primary key, unless you are allowing businesses to share the same hours with the join table - interestingly eventually you would have a finite amount of combinations; I'm not sure how many that would be :p

没有主键应该没问题，除非您允许企业与连接表共享相同的时间 - 有趣的是，最终您将拥有有限数量的组合；我不确定那会是多少：p

With one of my projects I used the columns:

在我的一个项目中，我使用了这些列：

[uInt]business_id, [uTinyInt]day, [char(11)]timeRange

[uInt]business_id, [uTinyInt]day, [char(11)]timeRange

If you want to support OpeningHoursSpecification then you'll need to add validFrom and validThrough.

如果您想支持 OpeningHoursSpecification，则需要添加 validFrom 和 validThrough。

Time Range is formatted like: hh:mm-hh:mm

时间范围的格式如下：hh:mm-hh:mm

Here's a function that parses it, you can also modify this function to parse just a single open/close, if you keep them as separate columns in the DB.

这是一个解析它的函数，如果您将它们作为单独的列保留在数据库中，您还可以修改此函数以仅解析单个打开/关闭。

Out of my experience I would recommend that you allow multiple times within a day, allow for a way to tell if they are explicitly closed on that day, or opened 24 hours or 24/7. I had mine say that if there was a day missing in the DB then the business was closed that day.

根据我的经验，我建议您在一天内允许多次，允许有一种方式来判断它们是在当天明确关闭，还是 24 小时或 24/7 开放。我曾说过，如果数据库中缺少一天，那么当天的业务就会关闭。

/**
 * parseTimeRange
 * parses a time range in the form of
 * '08:55-22:00'
 * @param $timeRange 'hh:mm-hh:mm' '08:55-22:00'
 * @return mixed ['hourStart'=>, 'minuteStart'=>, 'hourEnd'=>, 'minuteEnd'=>]
 */
function parseTimeRange($timeRange)
{
    // no validating just parsing
    preg_match('/(?P<hourStart>\d{1,2}):(?P<minuteStart>\d{2})-(?P<hourEnd>\d{1,2}):(?P<minuteEnd>\d{2})/', $timeRange, $matches);

    return $matches;
}

回答by Frans

It sort of depends on what you need to store it for and what the real-world data could look like.
If you need to be able to determine if the business is open at a certain point then it may be a bit awkward to query the scheme as laid out. More importantly, though, is: Would you ever need to cater for a mid-day closure?

这在某种程度上取决于您需要存储它的目的以及实际数据的外观。
如果您需要能够确定业务是否在某个时间点开放，那么查询计划中的方案可能会有点尴尬。不过，更重要的是：您是否需要满足中午关闭的需求？

Some options include;

一些选项包括；

• A scheme like what you have, but with the option to have multiple periods for the same day. It would cater for the lunch break, but would make it awkward to run a query that gives you the opening hours for a given day, say for presentation to a user.
• A bitmap style approach; "000000000111111110000000" for 9-5. The downside to this approach is that you have to choose a specific granularity, i.e. whole hours or half-hours or, indeed, minutes. The finer the granularity, the harder the data is to read for a human. You could use bitwise operators to store this value as a single number rather than a string of integers, but again it hurts legibility.

• 类似于您拥有的计划，但可以选择在同一天有多个时期。它可以满足午休时间的需求，但是如果运行查询来提供特定日期的营业时间（例如向用户演示），则会变得很尴尬。
• 位图风格的方法；9-5 的“000000000111111110000000”。这种方法的缺点是您必须选择特定的粒度，即整小时或半小时，甚至几分钟。粒度越细，数据就越难被人类读取。您可以使用按位运算符将此值存储为单个数字而不是整数字符串，但这又会损害易读性。

回答by Einstein

Might think about factoring in holidays by including additional fields for month of year/day of month/week of month. Week of month has some minor subtlties "last" could for example be week 4 or 5 depending on the year.

可能会考虑通过为年中的月份/月中的日/月中的一周添加额外的字段来考虑假期。一个月的一周有一些细微的“最后”可能是例如第 4 周或第 5 周，具体取决于年份。