在关系数据库中存储事件发生的一天中最好的方法是什么？ [英] What's the best way to store the days of the week an event takes place on in a relational database?
问题描述
我们正在为学校编写记录管理产品，其中一个要求是管理课程安排的能力。我没有看看我们如何处理这个代码（我现在处于不同的项目），但是我开始想知道如何最好地处理这个要求的一个特定部分，即如何处理这个事实每个课程可以在一周中的一天或多个星期，以及如何最好地将该信息存储在数据库中。为了提供一些上下文，一个裸骨头课程表可能包含以下列：
We're writing a records management product for schools and one of the requirements is the ability to manage course schedules. I haven't looked at the code for how we deal with this (I'm on a different project at the moment), but nonetheless I started wondering how best to handle one particular part of this requirement, namely how to handle the fact that each course can be held one or more days of the week, and how best to store this information in the database. To provide some context, a bare-bones Course table might contain the following columns:
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 列的最佳方式是什么？我遇到的问题是，这是一个多重的领域：也就是说，你可以在一周中的任何一天进行课程，同一课程可以在一天以上的时间内进行。我知道某些数据库本身支持多值列，但是有没有一个最佳实践来处理这个假设数据库本来不支持它？
What I'm wondering is, what is the best way to handle the DaysOfWeek column in this (contrived) example? The problem I'm having with it is that is a multi-valued field: that is, you can have a course on any day of the week, and the same course can take be held on more than one day. I know certain databases natively support multi-value columns, but is there a "best practice" to handle this assuming the database doesn't natively support it?
提出以下可能的解决方案到目前为止，但我想知道是否有任何更好的东西：
I've come up with the following possible solutions so far, but I'm wondering if anyone has anything better:
这是第一件弹出我的头（我不知道这是否是一件好事...）。在此解决方案中， DaysOfWeek 将被定义为一个字节，前7位将用于表示星期几（每天一位）。一个1位将表示一个班级在一周的相应日期举行。
This was the first thing that popped into my head (I'm not sure if that's a good thing or not...). In this solution, DaysOfWeek would be defined as a byte, and the first 7 bits would be used to represent the days of week (one bit for each day). A 1 bit would indicate that a class was held on corresponding day of the week.
优点：易于实施（应用程序可以处理
Pros: Easy to implement (the application can deal with the bit manipulations), works with any database.
缺点：难以编写使用 DaysOfWeek的查询 code>列（尽管您可以在应用程序级别处理这一点，或者在数据库中创建视图和存储过程以简化此操作），打破关系数据库模型。
Cons: Harder to write queries that use the DaysOfWeek column (although you could deal with this at the application level, or create views and stored procedues in the database to simplify this), breaks relational database model.
这与使用位字段基本上是一样的，而不是处理原始位，你为一周中的每一天分配一个唯一的字母，而 DaysOfWeek 列只存储一系列字母，指示课程持有的日期。例如，您可以将每个工作日与单字符代码相关联，如下所示：
This is essentially the same approach as using a bit field, but instead of dealing with raw bits, you assign a unique letter to each day of the week, and the DaysOfWeek column just stores a sequence of letters indicating what days a course is held on. For example, you might associate each weekday with a single-character code as follows:
Weekday Letter
------- ------
Sunday S
Monday M
Tuesday T
Wednesday W
Thursday R
Friday F
Saturday U
在这种情况下，星期一，星期二和星期五举行的课程对于 DaysOfWeek ，具有价值'MTF'，而仅在星期三举行的课程将有一个 DaysOfWeek 值'W'。
In this case, a course held on Monday, Tuesday, and Friday would have have the value 'MTF' for DaysOfWeek, while a class held only on Wednesdays would have a DaysOfWeek value of 'W'.
strong>：在查询中更容易处理（即，您可以使用 INSTR 或其等价物来确定某个类是否在某一天被持有）。适用于支持INSTR或等效功能的任何数据库（大多数，我猜...）。还可以查看，并且很容易看到使用 DaysOfWeek 列的查询发生了什么。
Pros: Easier to deal with in queries (i.e. You could use INSTR, or its equivalent, to determine if a class is held on a given day). Works with any database that supports INSTR or an equivalent function (most, I would guess...). Also friendlier to look at, and easy to see at a glance what is happening in queries that use the DaysOfWeek column.
缺点：唯一真正的con就是像bitfield方法那样通过在一个字段中存储可变数量的值来打破关系模型。
Cons: The only real "con" is that, like the bitfield approach, this breaks the relational model by storing a variable number of values in a single field.
另一种可能性是创建一个新表，存储所有独特的组合一周中的几天，并且 Course.DaysOfWeek 列只是这个查找表中的一个外键。然而，这个解决方案似乎是最不起眼的一个，我只是认为它是因为它似乎是关系方式 TM 来做事情。
Another possibility would be to create a new table that stores all the unique combinations of days of the week, and have the Course.DaysOfWeek column simply be a foreign key into this lookup table. However, this solution seems like the most inelegant one, and I only considered it because it seemed like the The Relational WayTM to do things.
优点：从关系数据库的角度来看，这是唯一的纯粹解决方案。
Pros: It's the only solution that is "pure" from a relational database point of view.
缺点 ：这是不方便和麻烦的。例如，您将如何设计用于将相应工作日分配给查找表周围的特定课程的用户界面？我怀疑用户想要按照星期日，星期日，星期一，星期日，星期一，星期二，星期日，星期一，星期二，星期三等方式处理选项，等等...
Cons: It's inelegant and cumbersome. For example, how would you design the user interface for assigning corresponding weekdays to a given course around the lookup table? I doubt a user wants to deal with choices alongs the lines of "Sunday", "Sunday, Monday", "Sunday, Monday, Tuesday", "Sunday, Monday, Tuesday, Wednesday", and so on...
那么，是否有更优雅的方式来处理单个列中的多个值？或者所提出的解决方案是否足够？对于什么是值得的，我认为我的第二个解决方案可能是我在这里概述的三种可能的解决方案中最好的，但我很好奇，看看有没有不同的意见（或者实际上是一种不同的方法）。 >
So, is there a more elegant way to handle multiple values in a single column? Or would one the proposed solutions suffice? For what it's worth, I think my second solution is probably the best of the three possible solutions that I outlined here, but I'd be curious to see if someone has a different opinion (or indeed a different approach altogether).
推荐答案
我会避免字符串选项的纯度感：它增加了一个额外的编码/解码层，你不需要。在国际化的情况下，也可能会让你失望。
I would avoid the string option for the sense of purity: it adds an extra layer of encoding/decoding that you do not need. It may also mess you up in the case of internationalization.
由于一周中的天数是7，我会保留七列，也许是布尔值。这也将有助于后续查询。如果工具在不同日子开始工作的国家/地区使用该工具，这也将非常有用。
Since the number of days in a week is 7, I would keep seven columns, perhaps boolean. This will also facilitate subsequent queries. This will also be useful if the tool is ever used in countries where the workweek starts on different days.
我将避免查找，因为这将过度归一化。除非您的查询项目不明显，否则可能会发生变化，否则这是非常有用的。在星期几的情况下（例如，与美国的州不同），我会用固定的睡眠睡眠。
I would avoid the lookup because that would be over-normalization. Unless your set of lookup items is not obvious or could possibly change, it's overkill. In the case of days-of-the-week (unlike US states, for example), I would sleep soundly with the fixed set.
考虑到数据域，我不认为一个位域可以为您节省大量空间，并且只会使代码更加复杂。
Considering the data domain, I don't think that a bitfield would achieve any significant space savings for you and would just make your code more complex.
最后，关于域名的一个警告词语：很多学校在他们的交换日的时间表上做了奇怪的事情，以平衡每学期平均每种类型的平日数尽管假期。我不清楚你的系统，但也许最好的方法是存储一个预期发生课程的实际日期的表格。这样一来，如果一周内有两个星期二，老师就可以得到两倍的报酬，而被取消的星期四的老师不会付款。
Finally, a word of warning about the domain: a lot of schools do weird things with their schedules where they "swap days" to balance out an equal number of weekdays of each type over the semester despite holidays. I am not clear about your system, but perhaps the best approach would be to store a table of the actual dates in which the course is expected to take place. This way, if there are two Tuesdays in a week, the teacher can get paid for showing up twice, and the teacher for the Thursday that was canceled will not pay.
这篇关于在关系数据库中存储事件发生的一天中最好的方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
