数小时运行的数据库设计有什么好方法？

I am trying to create an application that will need to use hours of operation and allow users to search by it (think how Yelp does this).

我正在尝试创建一个需要使用数小时操作并允许用户通过它进行搜索的应用程序（想想Yelp如何做到这一点）。

I am debating what is a good design for this. The only thing I can think of is having a database table with a foreign key to a "Companies" table that lists a day of the week and the open and close times.

我正在讨论什么是一个好的设计。我唯一能想到的是拥有一个数据库表，其中包含一个“公司”表的外键，该表列出了一周中的某一天以及打开和关闭时间。

Tbl_Hours_Operation
- day_of_week
- open_time
- close_time
- company_id

Is there any other approach that would work and be more efficient?

有没有其他方法可行，效率更高？

2 个解决方案

#1

Have two tables:

有两张桌子：

operational_hours (company_id, day_of_week, open_time, close_time)

operational_hours（company_id，day_of_week，open_time，close_time）

operational_hours_special (company_id, date, open_time, close_time)

operational_hours_special（company_id，date，open_time，close_time）

You would need to join the two tables to check for the special hours.

您需要加入这两个表来检查特殊时间。

Will any of your companies be closed for breakfast, lunch, dinner, siesta? If so, I would add:

您的公司是否会因早餐，午餐，晚餐，午睡而关闭？如果是这样，我会补充：

operational_hours_closed (company_id, day_of_week, close_time, open_time)

operational_hours_closed（company_id，day_of_week，close_time，open_time）

Even more fun JOINs!

更有趣的JOIN！

#2

Your approach seems sound. Just a few minor things to note:

你的方法看似合理。只需注意几个小问题：

Make sure you index this table on Company_Id and DayOfWeek_Id (and in that order). It should also (possibly) support multiple entries per day of week, in case the company closes during the day or if it is open overnight.

确保您在Company_Id和DayOfWeek_Id（并按此顺序）索引此表。它也应该（可能）支持一周中每天的多个条目，以防公司在白天关闭或者在一夜之间开放。

CompanyDayOfWeek
----------------
Company_Id     INT/BIGINT    FK->Company table
DayOfWeek_Id   INT           (this can be a FK or just a hard coded list of IDs)
Open_Time      TIME          if your DB supports a dateless time data type
Close_Time     TIME

#1