主页 / dba / 问题 / 145433
Accepted
DandyCC
Asked: 2016-07-31 14:03:38 +0800 CST

数据库设计。在不丢失参照完整性的情况下跟踪历史

  • 772

为简单起见，假设我们有这两个表：

CREATE TABLE `tbl_companies` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `tbl_workers` (
  `id` int(11) NOT NULL,
  `name` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

还有2张表用来存储公司和工人之间的关系

CREATE TABLE `tbl_companies_workers` (
  `company_id` int(11) NOT NULL,
  `worker_id` int(11) NOT NULL,
  PRIMARY KEY (`company_id `, `worker_id `)
);

CREATE TABLE `tbl_companies_workers_history` (
  `company_id` int(11) NOT NULL,
  `worker_id` int(11) NOT NULL,
  `start` date NOT NULL,
  `end` date DEFAULT NULL,
  PRIMARY KEY (`company_id `, `worker_id `)
)

使用此架构，可以轻松显示类似以下内容：“John Smith 从 2010 年到 2012 年在 MyComp 工作”。但是，如果从 de DB 中删除 MyComp，则该信息也会被删除。即使公司被删除，我也希望能够显示相同的信息。

是否有可能在不失去参照完整性的情况下实现这一目标？最好的方法是什么？我一直在寻找一些信息，我找到了 this和this，但我仍然不清楚。

提前致谢。

database-design referential-integrity

2 个回答

  1. Best Answer

    这是一个长期存在的问题，有许多笨拙的解决方案，例如您所引用的解决方案。最好的仍然存在参照完整性的问题。你如何引用一个随时间变化的实体并且你想维护所有版本？

    解决方案实际上非常简单：归一化。当我们规范化一个实体时，有一个主实体表。这包含实体的 PK 和所有其他属性。来自其他表的所有 FK 都引用此表，因为对于任何给定的实体只有一个条目。

    规范化后，该表仍然存在，但（至少）有一个包含某些属性的其他表。任何唯一实体可能有零个、一个或多个条目，但这没关系。其实这张表的PK就是实体的PK（也是回主实体表的FK）和一些其他的属性。

    所以现在你实现了 Version Normal Form。获取主要实体的所有属性并分离出那些随时间变化的属性，并且您想跟踪这些变化。您拥有的是主实体表，其中包含实体的唯一 PK（参照完整性正常工作）以及所有不随时间变化或不跟踪变化的属性。然后“版本”表包含所有更改和跟踪的属性。这张表的PK就是实体PK和change date（版本生效的日期和时间）。

    该方案的部分优点列表是

    • 它使用规范化，这是一个我们都知道并且每天都在使用的过程。
    • 参照完整性不受影响。
    • 当前和过去的版本在同一个表中。事实上，访问数据当前状态的查询和回顾过去的查询是同一个查询。
    • 对于那些提前知道状态更改的实例，更改日期可以设置为未来。在到达更改时间之前，此类计划的更改不会出现在查询中。

    可以使用两种“软删除”方法。可以将“已删除”日期时间属性添加到主记录，默认值为 NULL 或（我更喜欢）神奇日期 9999-12-31。这就是我所说的“坚定”删除，因为它不应该是可撤销的。另一种方法是在版本记录中添加一个“isDeleted”属性。删除操作会插入一个“isDeleted”设置为 TRUE（或“Y”或其他）的版本。删除只是另一个版本。因此，要取消删除，关闭“isDeleted”的新版本将使实体重新存在——删除它然后取消删除的时间就像任何其他状态更改一样保持不变。当然，如果需要的话，这两种方法都可以使用。

    这是我在技术展览会上多次制作的演示文稿的幻灯片。它涵盖了如何完成上述所有操作，包括查询。这是一份更详细的文件。

    • 3

  2. 在表中添加一个IsDeleted位字段并将其设置为TRUE删除行时，并在存储过程或应用程序代码中设置不显示IsDeleted行的规则。这样数据实际上并没有从数据库中删除。

    • 2

相关问题