在数据库中存储营业时间 [英] Storing Business Hours in a Database

问题描述

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

例如：

商家A有以下营业时间

• 星期一：上午9时至下午5时


• 星期二：上午9时至下午5时


• 星期三：上午9时至下午5时
• 星期五：上午9时至下午5时


• 星期六：上午9时至中午12时


• 星期日：休息

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

  CREATE TABLE business_hours（
idinteger NOT NULL PRIMARY KEY，
dayvarchar（16）NOT NULL，
open_timetime，
close_timetime 
）
  

其中day限制为一周7天的选择代码（通过ORM）。要测试某个业务在某一天是否关闭，它会检查open_time和close_time是否为NULL。它通过中间表（多对多关系）与业务相关。

有没有人对此数据库方案有任何建议？

解决方案

总的来说，我看不出有什么错。除了...

1. 我将使用您的本机编程语言使用的编号系统其图书馆）。这将减少数据库的大小并从您的代码中删除字符串比较。



2. 我可能将外键放在此表的业务表。

所以我想我会这么做：

  CREATE TABLEbusiness_hours（
idinteger NOT NULL PRIMARY KEY，
business_idinteger NOT NULL FOREIGN KEY REFERENCESbusiness，
dayinteger NOT NULL，
open_timetime，
close_timetime 
）
  / pre> 
 
 
在我的业务逻辑中，我将强制实施一个约束，即每个业务至少 7个工作时间。 （至少，因为Jon Skeet是对的，你可能想要休假时间。）虽然你可能想放松这个约束，只需在业务关闭的几天里关闭营业时间。 > 
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


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


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

Does any one have any suggestions for this database scheme? Something about it doesn't seem right to me.
解决方案
Overall, I see nothing wrong with this. Except...

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.
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屋！