代码之家 › 专栏 › 技术社区 › Mike Spross Alex Martelli

在关系数据库中存储事件发生一周中的几天的最佳方法是什么?

database-design

Mike Spross Alex Martelli · 技术社区 · 17 年前

我们正在为学校编写一个记录管理产品,其中一个要求是管理课程安排的能力。我还没有研究过我们如何处理这个问题的代码(目前我在一个不同的项目中),但是我开始想知道如何最好地处理这个要求的一个特定部分,即如何处理每门课程一周可以举办一天或几天的事实,以及如何最好地将这些信息存储在数据库中。为了提供一些背景,一个简单的 Course 表可能包含以下列:

Course          Example Data
------          ------------

DeptPrefix      ;MATH, ENG, CS, ...
Number          ;101, 300, 450, ...
Title           ;Algebra, Shakespeare, Advanced Data Structures, ...
Description     ;...
DaysOfWeek      ;Monday, Tuesday-Thursday, ...
StartTime       
EndTime

我想知道的是,什么是处理 DaysOfWeek 这个(人为的)例子中的列?我的问题是,这是一个多值的领域:也就是说,你可以在一周中的任何一天上一门课,同一门课可以在一天以上举行。我知道某些数据库本机支持多值列,但假设数据库本机不支持,是否有“最佳实践”来处理这一问题?

到目前为止,我已经提出了以下可能的解决方案,但我想知道是否有人有更好的解决方案:

可能的解决方案1:将daysofweek视为位字段

这是第一件突然出现在我脑海中的事情(我不确定这是不是好事…)。在这个解决方案中, 星期天 将被定义为一个字节,前7位用于表示一周中的几天(每天一位)。1位表示在一周中的相应日期举行了一个课程。

赞成的意见 :易于实现(应用程序可以处理位操作),适用于任何数据库。

欺骗 :难以编写使用 星期天 列(尽管您可以在应用程序级别处理此问题,或者在数据库中创建视图和存储过程以简化此问题),但它破坏了关系数据库模型。

可能的解决方案2:将daysofweek存储为字符串

这基本上与使用位字段的方法相同,但不是处理原始位,而是为一周中的每一天分配一个唯一的字母,并且 星期天 列只存储一系列字母,指示课程的持续时间。例如,您可以将每个工作日与以下单个字符代码关联:

Weekday      Letter
-------      ------

Sunday       S
Monday       M
Tuesday      T
Wednesday    W
Thursday     R
Friday       F
Saturday     U

在这种情况下,周一、周二和周五举行的课程将具有 'MTF' 对于 星期天 如果只在星期三上课的话 星期天 价值 'W' .

赞成的意见 :在查询中更容易处理(即您可以使用 INSTR 或其等价物,以确定某个班级是否在某一天举行)。使用任何支持instr或等效函数的数据库(大多数情况下,我想是…)。也更友好地查看,并且很容易一目了然地看到使用 星期天 列。

欺骗 :唯一真正的“con”是,与bitfield方法一样,它通过在单个字段中存储可变数量的值来破坏关系模型。

可能的解决方案3:使用查找表(丑陋)

另一种可能是创建一个新表,该表存储一周中所有天的唯一组合,并具有 Course.DaysOfWeek 列只是此查阅表格的外键。然而,这个解决方案似乎是最不雅的,我之所以考虑它是因为它看起来像是关系方式 ^TM 做事。

赞成的意见 :从关系数据库的角度来看,它是唯一“纯”的解决方案。

欺骗 :它不漂亮而且笨重。例如,如何设计用户界面,以便为查找表周围的给定课程分配相应的工作日?我怀疑用户是否希望处理“星期日”、“星期日、星期一”、“星期日、星期一、星期二”、“星期日、星期一、星期二、星期三”等行中的选项…

其他想法?

那么,在一列中处理多个值有没有更优雅的方法呢?或者,建议的解决方案是否足够?就其价值而言,我认为我的第二个解决方案可能是我在这里概述的三个可能的解决方案中最好的一个,但我很好奇是否有人有不同的意见(或者实际上是完全不同的方法)。

6 回复 | 直到 10 年前

Uri 17 年前

我将避免使用字符串选项来表达纯洁感:它添加了一个您不需要的额外编码/解码层。在国际化的情况下,它也可能会把你搞得一团糟。

因为一周的天数是7,所以我会保留7列,也许是布尔值。这也将有助于后续查询。如果在工作周从不同的日期开始的国家使用该工具,这也将非常有用。

我将避免查找,因为这将过度规范化。除非您的一组查找项不明显或可能发生更改,否则这是多余的。以一周中的几天为例(例如,与美国不同),我会在固定的环境下睡得很好。

考虑到数据域,我认为位域不会为您节省大量的空间,只会使代码更加复杂。

最后,一句关于这个领域的警告:很多学校在他们的日程表上做一些奇怪的事情,在这些日程表中,尽管假期,他们“交换天数”来平衡每种类型的工作日数量。我不清楚您的系统,但最好的方法可能是存储一个实际日期表,在其中课程预计发生。这样,如果一个星期有两个星期二,老师两次出现就可以得到报酬,而被取消的星期四的老师将不会得到报酬。

Nelson Teixeira 10 年前

我认为如果我们使用位选项,就不难编写查询。只需使用简单的二进制数学。我认为这是最有效的方法。就我个人而言,我总是这样做。看一看:

 sun=1, mon=2, tue=4, wed=8, thu=16, fri=32, sat=64.

现在,假设课程在周一、周三和周五举行。要保存在数据库中的值为42(2+8+32)。然后您可以在星期三这样选择课程:

select * from courses where (days & 8) > 0

如果你想上周四和周五的课程,你可以写:

select * from courses where (days & 48) > 0

这篇文章是相关的: http://en.wikipedia.org/wiki/Bitwise_operation

您可以将星期几作为常量放在代码中,这样就足够清楚了。

希望它有帮助。

Nicholas Piasecki 17 年前

一个可能的4:为什么它需要是一个单独的列?您可以将一周中的每一天的7位列添加到表中。用它编写SQL很简单,只需在您选择的列中测试1即可。从数据库中读取的应用程序代码只是将其隐藏在一个开关中。我意识到这不是正常的形式,我通常会花相当多的时间尝试从以前的程序员那里撤销这种设计,但我有点怀疑我们是否会在不久的将来给这周增加第八天。

为了对其他解决方案发表评论,如果遇到查找表,我可能会呻吟。我的第一个倾向是位字段和一些自定义数据库函数,以帮助您轻松地针对该字段编写自然查询。

我很想看看人们提出的其他一些建议。

编辑:我应该添加3,上面的建议更容易添加索引。我不知道如何为不会导致表扫描的1或2查询编写类似“在星期四获取所有类”的SQL查询。但今晚我可能只是昏昏欲睡。

Vincent Ramdhanie 17 年前

3号解决方案似乎最接近我的建议。查找表概念的扩展。每门课程都有一个或多个课程。创建具有以下属性的会话表:课程ID、日期、时间、讲师ID、房间ID等。

现在,您可以为每门课程的每一节课分配不同的讲师或房间,假设您以后可能希望存储这些数据。

如果您正在考虑最佳的数据库设计,那么用户界面问题就不相关。您可以始终创建用于显示数据的视图,对于捕获数据,您的应用程序可以处理为每个课程捕获多个会话并将它们添加到数据库的逻辑。

这些表的含义会更清楚,这使得长期维护更容易。

Powerlord 17 年前

如果选择一个或两个,则表将不为1nf(第一个正常形式),因为它包含多值列。

尼古拉斯有一个很好的想法,尽管我不同意他的想法打破了最初的正常形式:数据实际上不是重复的,因为每天都是独立存储的。唯一的问题是您必须检索更多的列。

James Anderson 17 年前

如果性能是一个问题,我建议更清洁的变量为3。

将课程链接到“日程”表。

它依次链接到“日程”表中的“天”。

_schedule表中的days_列包含日程名称和_schedule_day中的日期。该计划中的每个有效日期都有一行。

你需要一些时间来操作一些聪明的程序来填充这个表,但是一旦完成了这个操作, 灵活性是值得的。

您不仅可以应付“仅星期五的课程”,还可以应付“仅第一学期”、“第三学期实验室关闭整修”和“加拿大分公司假期安排不同”。

其他可能的查询是“从4月1日开始的20天课程的结束日期是什么”,“日程冲突最严重”。如果你真的很擅长SQL,你可以问“对于一个已经预订了YYY课程的学生,XXX课程可能会有几天时间开放”——我觉得这是你所提议的系统的真正产物。