mysql - M :N table with time interval 的数据库设计

Question

我想问你一个设计问题:

我正在设计一张让我挠头的 table ，不确定最好的方法是什么，我觉得我错过了什么:

A、B两张表，一张M:N关系表。关系表现在有这些值:A.ID，B.ID，从，到

业务要求:在任何时候，A:B 关系只能是 1:1A:B 可以按 From 和 To 日期时间值定义的时间重复，这些值指定一个间隔

示例:汽车/司机。任何汽车在任何时候都只能有 1 名司机任何车手在任何时候都只能驾驶一辆车(这不是顶级车，好吗？:))司机可以在一段时间后换车，也可以返回原车

现在，我不确定:- 我应该和什么PK一起去？ A,B不够，加上From和To感觉不对，可能是自增PK？-有什么方法可以通过数据库设计来执行业务需求？-出于商业原因，我不希望它出现在历史表中。为什么？好吧，假设这辆车是租来的，我想知道给定一个日期，谁在那个日期租了什么车。将它拆分成历史表需要更多的连接:(

我觉得我错过了一些东西，某种普遍的模式......或者我不知道......

感谢任何帮助，非常感谢:)

Answer 1

我认为您实际上并没有遗漏任何东西。我认为您已经掌握了问题所在。

我读过几篇关于如何在关系数据库中处理“时态”数据的文章。

基本共识是，传统的关系模型没有任何内置机制来支持时态数据。

有多种方法，有些方法比其他方法更适合特定要求，但所有方法都感觉像是“胶带”。

(我本来想说“插上了”，但我认为红绿帽子的一角是有条理的:“……勤杂工的 secret 武器，胶带”，以及“如果女人不觉得你帅，他们至少应该觉得你得心应手。”)

---

就表的 PRIMARY KEY 或 UNIQUE KEY 而言，您可以使用 (a_id, b_id, from) 的组合。这将为该行提供一个唯一标识符。

但是，这对防止“时间”范围重叠没有任何作用。

MySQL 表没有声明性约束来防止存储为“开始”、“结束”或“开始”、“持续时间”等的“重叠”日期时间范围(至少，在一般情况下。如果您定义的范围非常明确，并且触发器将 from 舍入为偶数四小时的边界，并且持续时间恰好为四小时，则可以使用 UNIQUE 约束。在更一般的情况下，对于from 和 to 的任何 ol' 值，UNIQUE 约束对我们不起作用。

CHECK 约束是不够的(因为您需要查看其他行)，即使可能，MySQL 也不会实际执行检查约束。

让数据库强制执行此类约束的唯一方法(我知道)是 TRIGGER 查找受影响(插入/更新)行的另一行的存在冲突。

您需要一个 BEFORE INSERT 触发器和一个 BEFORE UPDATE 触发器。触发器需要查询表，以检查是否存在与新/修改的行“重叠”的行

SELECT 1
  FROM mytable t
 WHERE t.a_id = NEW.a_id
   AND t.b_id = NEW.b_id
   AND t.from <> OLD.from
   AND < (t.from, t.to) overlaps (NEW.from,NEW.to) >

显然，最后一行是所需的实际语法的伪代码。

之前的行只在 BEFORE UPDATE 触发器中需要，所以我们找不到(作为“匹配项”)正在更新的行。那里的实际检查实际上取决于 PRIMARY KEY(或 UNIQUE KEY)的选择。

在 MySQL 5.5 中，如果我们发现新的/更新的行违反约束，我们可以使用 SIGNAL 语句返回错误。对于以前版本的 MySQL，我们可以通过执行导致实际错误发生的操作来“抛出”错误，例如针对我们知道不存在的表名运行查询。

最后，这种类型的功能不一定要在数据库触发器中实现；这可以在客户端处理。