数据库设计——显示可用时间的最佳方式?

I am interested in seeing suggestions for a database design regarding business hours.

我有兴趣看到关于工作时间的数据库设计的建议。

It would be quite similar to what Facebook has - alt text http://uploader.ws/upload/200903/widget.png

它与Facebook的alt文本http://uploader.ws/upload/200903/widget.png非常相似

I have a list of businesses, and I would like for users to be able to input multiple sets of available hours for that business. e.g.,

我有一个业务列表，我希望用户能够为该业务输入多组可用时间。例如,

Monday: open 9-5; Tuesday: open 9-12; 1-5; etc. I would not like to be limited to two sets of hours per day. Ideally, N sets of hours per day. If that's not practical, no more than 4... no less than 2.

星期一:开放9 - 5;星期二:开放9 - 12;1 - 5;我不愿意每天被限制在两组小时之内。理想情况下，每天有N个小时。如果那不实际，不超过4……不少于2。

I am interested in the "best" (theoretical) and the most practical solutions.

我对“最佳”(理论)和最实用的解决方案感兴趣。

The DBMS I'm using is MySQL.

我使用的DBMS是MySQL。

4 个解决方案

#1

How about:

如何:

create table business (
  id int not null auto_increment primary key,
  name varchar(255)
);

create table open_hour_range (
  id int not null auto_increment primary key,
  business_id int,
  day_of_week tinyint, /* 0-6 */
  open_time time,
  close_time time,
  foreign key(business_id) references business(id)
);

This allows you any combination of hours, including multiple per day. However, it may be a bit slow from a querying perspective, in that you'll need to do a fair amount of joining to come up with the list of what hours a business is open.

这可以让你的任何时间组合，包括每天多个小时。然而，从查询的角度来看，它可能有点慢，因为您需要做大量的加入，才能列出一个企业的营业时间列表。

Also, if you want to be able to display hours in a format like:

此外，如果您希望能够以以下格式显示小时数:

M-F 9-5 Sa-Su 9-12

M-F 9 - 5 Sa-Su 9 - 12

You'd need to merge similar ranges in code, outside the database. If you wanted this sort merging, you could change day_of_week to a start_day and an end_day.

您需要在数据库之外的代码中合并类似的范围。如果希望合并这种排序，可以将day_of_week改为start_day和end_day。

#2

A minor tweak to Scotty Allen's model:

斯科蒂·艾伦模型的一个小改动:

business table:
id - int
business_name - string
open_hour_range table:
id - int
business_id - int //foreign key to business
days_of_week- int // (bitmask) 1-127
open_time - time
close_time - time

业务表:id - int business_name - string open_hour - range表:id - int business_id - int //foreign key to business days_of_week- int /(位掩码)1-127 open_time - time close - time - time - time - time - time

#3

With Microsoft SQL Server database you could store that data as typed XML and still be able to sort and serach data based on one or more values in that field. Make any calculations if needed etc.

使用Microsoft SQL Server数据库，您可以将这些数据存储为类型化XML，并且仍然能够根据该字段中的一个或多个值对数据进行排序和serach。如果需要做任何计算等等。

A value in that column could look similar to this:

该列中的一个值可以类似如下:

<businessDays>
    <monday>
        <hours from="12" to="15" />
        <hours from="16" to="21" />
    </monday>
    <friday>
        <hours from="13" to="15" />
        <hours from="16" to="18" />
        <hours from="19" to="21" />
    </friday>
</businessDays>

References on Typed XML:

引用类型的XML:

Typed XML in SQL Server 2005
在SQL Server 2005中键入XML
Typed XML Compared to Untyped XML
与非类型化XML相比，类型化XML。

References on indexing and quering XML data in SQL Server:

SQL Server中关于索引和查询XML数据的参考文献:

XML Queries and Indexing in SQL Server 2005
SQL Server 2005中的XML查询和索引。
SQL Server 2005 XQuery and XML-DML - Part 1
SQL Server 2005 XQuery和XML-DML -第1部分。

In your application you could serialize/deserialize this data into and from business objects.

在您的应用程序中，您可以将这些数据序列化/反序列化到业务对象中或从业务对象中序列化。

Extremely simple and efficient.

非常简单和有效的。

#4

I wolud rather use datetime in columns open_time and close_time. Its for meetings that will start at night and will end in the morning next day. :)

我宁愿在open_time和close_time列中使用datetime。会议将在晚上开始，第二天早上结束。:)

#1