开放时间数据库设计

Question

我们目前正在开发一个应用程序，其中多个实体具有关联的开放时间。开放时间可能跨越多天，也可能在一天之内。

前任。周一 6:00 开市，周五 18:00 闭市。

或者

周一 06:00 开放，周一 15:00 关闭。

此外，一个实体每天可能有多组开放时间。 到目前为止，我发现的最好的设计是定义一个开放时间，由以下内容组成：

StartDay、StartTime、EndDay 和 EndTime。

这种设计可以实现所有所需的灵活性。然而，数据完整性成为一个问题。我似乎找不到一个不允许重叠跨度（在数据库中）的解决方案。

请分享您的想法。

编辑：数据库是 Microsoft SQL Server 2008 R2

Answer 1

考虑存储您的 StartDay 和 StartTime，然后为其打开的小时数设置一个值。这将确保您的截止日期时间在开盘之后。

OpenDate -- day of week? e.g. 1 for Monday
OpenTime -- time of day. e.g. 08:00
DurationInHours -- in hours or mins. e.g.   15.5

Answer 2

假设有一个强大的触发器框架，

在插入/更新时，您将检查新的开始或结束日期是否在任何现有范围内。如果确实如此，那么您将回滚更改。

CREATE TRIGGER [dbo].[mytable_iutrig] on [mytable] FOR INSERT, UPDATE AS

IF (SELECT COUNT(*)
FROM inserted, mytable
WHERE (inserted.startdate < mytable.enddate 
          AND inserted.startdate > mytable.startdate)
      OR (inserted.enddate < mytable.enddate 
          AND inserted.enddate > mytable.startdate)) > 0 
BEGIN
    RAISERROR --error number
    ROLLBACK TRANSACTION
END

Answer 3

检测和防止重叠时间段必须在应用程序级别完成。当然，您可以尝试在数据库中使用触发器，但在我看来这不是数据库问题。您想出的结构很好，但是您的应用程序逻辑必须处理重叠问题。

Answer 4

SimpleTalk 网站上有一篇 Joe Celko 撰写的文章，内容为 这里讨论了类似的问题，并提出了一个优雅但复杂的解决方案。这可能适用于您的情况。

Answer 5

具有单列 TimeOfChangeBetweenOpeningAndClosing 的表？

但更严重的是，我可能不会太担心提出一个单一的数据库结构来表示所有内容，最终您可能想要一个涉及重复、计划关闭等的系统。持久化表示这些的对象，然后评估它们以找到关闭/开放时间。

Answer 6

这看起来是一个很好的解决方案，但您必须编写一个自定义验证函数。内置的数据库验证（即唯一、小于 x 等）不会在这里解决它。为了确保没有重叠的跨度，每次将记录插入数据库时​​，您都必须选择现有记录并进行比较...

Answer 7

首先是逻辑，如果一个跨度的起始值落在另一个跨度的开始/结束之间，则两个跨度将重叠。如果我们将日期时间组合起来，而不是日期 1、时间 1 和日期 2、时间 2，这会容易得多。因此，查找重叠的查询如下所示。

select openingId
  from opening o1
  join opening o2 on o1.startDateTime 
             between o2.startDateTime
                 AND o2.endDateTime

您可以将其放入触发器中，并在找到匹配项时抛出错误。