SQL面试题：如何防止日期范围重叠

Question

假设有一个名为appointment_booking的表格，其中包含了经理（或人力资源）的列表，以及他们的开始时间和结束时间。那么如何精心设计该表格，使其不会接受与同一经理已有约会冲突的记录呢？

例如： 如果经理A在2016年1月1日11:00到14:00与员工1有约定， 那么如果员工2（或其他员工）试图在2016年1月1日13:00到16:00之间预约，则不允许。

注：这是关于设计表格，所以不鼓励使用触发器/存储过程等方法。

- user2518

---

为了强制执行这个约束条件，你需要一个触发器或用户定义的函数（至少在大多数数据库中是这样）。 - Gordon Linoff

如果这确实是一个SQL面试问题，你可以简单地回答，在设计时它是不可能的。 - Jorge Campos

如果您还建模了空闲时间段，具有交叉引用外键将每个时间段链接到其前身和后继，并编写一些真正可怕的“MERGE”语句来管理插入/删除（在这里，更多时候，您需要通过更新一个自由时间段、插入新行并在单个语句中插入新约会来拆分自由时间段，以便满足外键），则可以在SQL Server中完成此操作。它是可行的，但通常成本高于价值。 - Damien_The_Unbeliever

Answer 1

不必插入范围，而是可以插入时间片段。您可以将时间片段设置为任意宽度，但假装您每次只能预订30分钟的经理。要从11:30到12:00预订，请插入具有时间值11:30的行。要从11:30到12:30预订，则需插入两行，一行在11:30，另一行在12:00。然后，您只需使用主键约束或唯一约束来防止超订。

create table appointment_booking (
    manager char not null,
    startSlice DateTime,
    visiting_employee varchar2(255),
    primary key (manager, startSlice)
)

我知道这并不完全符合您关于具有起始时间和结束时间的表格的前提条件，但如果您可以控制表格结构，那么这将有效。

- Chris Steele

Answer 2

检查约束 + 函数（这是我能够接近DDL答案的方式）

您可以创建一个标量函数 - "SCHEDULE_OPENING_EXISTS()"，它以开始时间、结束时间和员工ID作为输入，并输出true或false。

然后，您可以在表上创建一个检查约束。

CREATE TABLE...
    WITH CHECK ADD CONSTRAINT OPENING_EXISTS
    CHECK (SCHEDULE_OPENING_EXISTS(begin, end, employeeID)) = 'True')

触发器：

我尽量避免使用触发器。它们本身并不是邪恶的，但它们确实给应用程序增加了新的复杂度层面。如果你无法避免使用触发器，你需要一个INSTEAD OF INSERT，以及一个INSTEAD OF UPDATE（大概率）。参考Technet：https://technet.microsoft.com/en-us/library/ms179288%28v=sql.105%29.aspx

请记住，如果您拒绝插入/更新尝试，您需要决定是否以及如何将其通知给用户。

存储过程/用户界面：

存储过程是否适用于您的情况？以下是一个示例场景：

1. 用户界面--用户需要查看他们预约的人员的日程安排。

2. 从用户界面--尝试使用存储过程进行插入/更新。在最后一分钟重新检查开放情况（如果开放不存在则返回失败），然后有条件地插入/更新（返回成功消息）。

如果过程向用户界面返回失败，请在用户界面中处理，通过重新查询所有参与方的可见日程表并附带错误消息来处理。

- Chains

---

注意：问题涉及检查约束、数据建模和SQL中的DDL，与应用程序或UI无关，存储过程也不相关。 - wildplasser

@wildplasser -- 在答案中添加了一个CHECK CONSTRAINT选项...虽然不完全是DDL，但更接近了。 - Chains

Answer 3

我认为这些类型的问题很有趣，因为在设计数据库时，了解将与您的数据库交互的应用程序的要求非常重要。

话虽如此，只要应用程序可以引用多个表，我认为 Chris Steele 的回答是一个很好的起点，我会在其基础上进行改进...

我需要两个表。第一个表将一天分成几个部分（切片），具体取决于组织的业务需求。每个切片都将是该表的主键。我个人会选择15分钟的时间段，相当于96个日段。该表中的每个日段都将有“块开始”和“块结束”时间，当用户选择实际开始时间和实际结束时间以进行会议安排时，调度应用程序将引用这些时间。应用程序需要应用逻辑（例如，在3个“AND”语句之间插入两个“OR”运算符），以查看特定的 blockID 是否将被插入到您的约会表中：

• 实际开始时间 >= 块开始时间且实际开始时间 < 块结束时间
• 实际结束时间 > 块开始时间且实际结束时间 < 块结束时间
• 实际开始时间 < 块开始时间且实际结束时间 > 块结束时间

这与Chris Steele的答案略有不同，因为它使用了两个表。实际时间戳仍然可以插入到您的应用程序表中，但只有在与TimeBlocks表进行比较时才对它们应用逻辑。在我的约会表中，我更喜欢将日期分解为组成部分，以进行跨平台分析（我们的组织使用多个RDBMS以及SAS进行分析）：

CREATE TABLE TimeBlocks (
          blockID Number(X) NOT NULL,
          blockStart DateTime NOT NULL,
          blockEnd DateTime NOT NULL,
     primary key (blockID)
);


CREATE TABLE Appointments (
          mgrID INT NOT NULL,
          yr INT NOT NULL,
          mnth INT NOT NULL,
          day INT NOT NULL,
          blockID INT NOT NULL,
          ApptStart DateTime NOT NULL,
          ApptEnd DateTime NOT NULL
          empID INT NOT NULL,
     primary key (mgrID, yr, mnth, day, blockID),
     CONSTRAINT timecheck
          check (ApptStart < ApptEnd)
);

- dreamwork