关系数据库中的时间表建模
我知道几乎所有与 RDBS 中的时间表建模相关的内容都被告知，但我找不到任何关于在数据库中存储时间表的可用技术的书面文档。
我的情况：
- 我有一个包含可用位置的桌子，以及一个包含实际课程的桌子。
- 每个地方都有自己独特的时间表
- 每个课程都可以在任何地点、任何时间安排，但很少有例外：
- 一堂课可以占用一个时间段（例如：如果 A 课安排在 12:00 地点 P1，持续 1 小时，则下一次 A 课只能安排在 12:00 之前或 13:00 之后，任意地点，有空闲时段的，禁止两地同时安排A班）
- 在一个地方可以是一个有时间段的班级
- 模型应该支持已安排课程的版本控制/历史记录
现在，我怎样才能在 SQL DB 中表示此数据模型？
我并不是在寻找现成的精确模式，而是如果有人可以编写可用的建模技术及其比较，我会很高兴，我可以用它来解决这个任务
例如：对于树 -结构/分层数据，有详细记录的“修改的先序树遍历算法”，是否有一些类似的算法/技术来处理时隙？
I know that it has been told almost anything related to time table modeling in RDBS, but I can not find any well written documentation about available techniques to store time tables in DB.
My case:
- I have table which holds available places, and table with actual classes.
- Each place has it's own unique schedule
- Each class can be scheduled in any place, and any time, with few exceptions:
- One class can take one time-slot (Example: If class A is scheduled in place P1 at 12:00 for 1hour duration, next occurrence of class A can only be placed before 12:00 or after 13:00, in any place, which has free time-slot; It's forbidden to schedule class A in one time in two places)
- In one place it can be one class with time-slot
- Model should support versioning/history of scheduled classes
Now, how I can represent this data model in an SQL DB?
I'm not looking ready-to-use exact schema, rather I will be glad if anyone can write available modelling techniques and their comparison, which I can use to solve this task
For example: For tree-structure/hierarchical data, there is well documented "modified preorder tree traversal algorithm", is there some similar algorithm/technique to deal with time-slots?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
时间表是一个矩阵。左侧有“地点”。在顶部我们有时间段。 LOCATION 和 TIMESLOT 的任何给定排列的交集是具有 CLASS 或 null 的单元格。
为了对此进行建模，我们需要一个位置表（实体），这是非常固定的数据。我们需要一个不断增长的 TIMESLOTS（日期/时间）表。我们需要一个表 CLASSES，它也是相当固定的。最后我们需要一个交集表 CLASS_TIMESLOT_LOCATIONS。这就是奇迹发生的地方。该表有 3 个外键，1 个外键为 CLASSES，1 个外键为 LOCATIONS，1 个外键为 TIMESLOTS。它的主键是（LOCATION_ID，TIMESLOT_ID），但它还需要对（CLASS_ID，TIMESLOT_ID）进行唯一约束。
您正在询问一个建模问题，但您需要考虑一些实现细节。它们不会改变逻辑模型，但会影响您使用物理表的方式。首先要考虑的是是否生成所有潜在的时间段，如果是，您存储的窗口有多大。第二个是是否为交集表 CLASS_TIMESLOT_LOCATIONS 存储空条目。
这里没有直接的答案：某些数据库产品会发现比其他产品更容易“填补空白”。此外，动态生成缺失的记录可能会对性能造成太大影响，在这种情况下，磁盘空间是一个很好的权衡。
至于存储历史记录，这大概是为了存储对时间表的更改。为此，请使用单独的表，并由触发器填充（您可以使用存储过程，但触发器是行业标准）。不要试图将历史记录存储在主表中。它打破了正常化的模式并引起了各种各样的悲伤。
A timetable is a matrix. Down the left hand side we have LOCATIONS. Across the top we have TIMESLOTS. The intersection of any given permutation of LOCATION and TIMESLOT is a cell with either a CLASS or null.
To model this we need a table (entity) of LOCATIONS, which is pretty fixed data. We need a table of TIMESLOTS (date/times) which is ever growing. We need a table CLASSES, which is also pretty fixed. Finally we need an intersection table CLASS_TIMESLOT_LOCATIONS. This is where the magic happens. This table has three foreign keys, one to CLASSES, one to LOCATIONS, one to TIMESLOTS. Its primary key is (LOCATION_ID, TIMESLOT_ID) but it also needs a unique constraint on (CLASS_ID, TIMESLOT_ID).
You are asking a modelling question, but there are a couple of implementation details which you will need to think about. They won't chnage the logical model but they will affect how you work with the physical tables. The first consideration is whether to spawn all the potential TIMESLOTS, and, if so, how big a window you store. The second is whether to store null entries for the intersection table, CLASS_TIMESLOT_LOCATIONS.
There are no straightforward answers here: some database products will find it easier to "fill in the gaps" than others. Also, generating the absent records on the fly may be too much of a performance hit, in which case disk space is a good trade-off.
As for storing history, this is presumably for storing changes to the schedule. Use separate tables for this, populated by triggers (you could use stored procedures but triggers is the industry standard). Don't be tempted to store history in the main tables. It breaks the normalised model and causes all sorts of grief.
从我在您的问题中看到的情况来看，您似乎有几个希望在数据库方面处理的约束。
• 我有一张桌子，里面有可用的位置，还有一张桌子，里面有实际的课程。
可以对表设计进行更多详细说明，但这只需要一个表模式来保存您需要的信息
• 每个地方都有自己独特的时间表
如何在插入上创建触发器以确保正在插入到时间表中的类不与其他日程冲突吗？
• 每节课都可以安排在任何地点、任何时间，但很少有例外：
• 一堂课只能占用一个时间段（例如：如果 A 课安排在 12:00 地点 P1，持续 1 小时，则下一次 A 课只能安排在 12:00 之前或 13:00 之后，在任何地点，女巫有空闲时间段；禁止在两个地方同时安排A类）
我也会在触发器中处理这个约束
•在一个地方它可以是一个有时间段的类
在触发器中处理这个约束
• 模型应该支持计划类的版本控制/历史记录
有一个单独的表，它反映了计划的实际表。当新记录插入主表时，您可以触发具有历史记录的表中的更新/插入/删除的更新和时间
希望这对您有一些帮助。
-维杰
From what I see in your question it looks like you have several constraints you would like to have handled on the database side.
• I have table which holds available places, and table with actual classes.
More can be elaborated on the table design, but this just needs a table schema to hold the information you need
• Each place has it's own unique schedule
How about creating a trigger on inserts to make sure that the class that is being inserted into the schedule does not conflict with any other schedule?
• Each class can be scheduled in any place, and any time, with few exceptions:
• One class can take one time-slot (Example: If class A is scheduled in place P1 at 12:00 for 1hour duration, next occurrence of class A can only be placed before 12:00 or after 13:00, in any place, witch has free time-slot; It's forbidden to schedule class A in one time in two places)
I would handle this constraint in a trigger also
• In one place it can be one class with time-slot
Have this constraint handled in a trigger
• Model should support versioning/history of scheduled classes
Have a separate table which mirrors the actual table that you have for schedules. As new records get inserted into the main table, you can trigger the updates and times for the updates/inserts/deletes into the table which has the history
Hope this helps you with some ideas.
-Vijay