在数据库中存储业务时间

我目前正在尝试找出在数据库中存储业务运行时数的最佳方法。

例如:

业务 A 有以下营业时间

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

目前我有一个类似于下面这样的数据模型

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

其中“一天”仅限于在代码中(通过 ORM)选择一周中的7天。为了测试企业是否在某一天关闭，它检查 open _ time 和 close _ time 是否为 NULL。它通过一个中间表(多对多关系)与业务相关。

有人对这个数据库方案有什么建议吗? 我觉得有点不对劲。

46574 次浏览
小开
最佳答案

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.

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 least 7 "business hours". (At least because 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.

小开

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.
小开

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.

小开

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.

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.

小开

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

https://schema.org/openingHours

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

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

With one of my projects I used the columns:

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

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

Time Range is formatted like: 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.

/**
* 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;
}
小开

Most of results works fine for the given scenario, but it wont be as effective if you have periods that runs through multiple days, eg. 8:00 AM ~ 2:00 AM, then I recommend using a multi period design.

    {
id: 1,
day: 1,
periods: [
0: { open: 08:00, close: 00:00 }
]
},
{
id: 2,
day: 2,
periods: [
0: { open: 08:00, close: 00:00 }
1: { open: 00:00, close: 02:00 }
]
}

day: number of day of the week
if no periods, means it is closed


提交答案