一个永不删除的关系型数据库模式设计

Question

我正在考虑为一个从不真正删除任何东西的数据库设计一个关系型数据库模式（可以设置删除标志或者其他）。

1）通常使用哪些元数据列来适应这种架构？显然，可以设置一个IsDeleted的布尔标志。或者仅在一个Deleted列中放置一个时间戳会更好，或者可能两者都行。我不确定哪种方法会在长期运行中给我带来更多问题。

2）这种架构通常如何处理更新？如果将旧值标记为已删除并插入一个新值，则会遇到PK唯一约束问题（例如，如果您有PK列ID，则新行必须具有与刚刚标记为无效的行相同的ID，否则该ID在其他表中的所有外键都将无用）。

- Davis Dimitriov

---

如果你有一个表A，它有一个外键约束指向表B，并且表B中的一行被删除了，你希望发生什么？我们是否假设A中引用的行已经被删除了？A应该指向B中的新行吗？A中的行是否仍然存在并继续指向已删除的B中的行？ - Tim Gautier

Answer 1

如果您的目标是审计，我会为每个表创建一个影子表。添加一些触发器，在更新和删除时触发，并将行的副本插入到影子表中。

- Steve Prentice

---

@btilly：并非完全正确；如果您关心性能，并且恰好希望性能变慢，那么这也是一个不错的解决方案。 :) - Paul Sonier

什么性能？选择、插入、更新、删除？ - Conrad Frix

原始问题暗示在UPDATE和DELETE时会进行复制。该问题还标记了许多RDBMs，因此不应使用任何特定于供应商的内容。 - Steve Prentice

@Conrad Frix：触发器只在插入、更新和删除时运行，因此这些操作会变慢。 - btilly

@Steve Prentice：触发器的实现往往高度依赖于数据库，但它们在大多数数据库中都是可用的。即使是MySQL和SQLite也有触发器！ - btilly

Answer 2

我认为你在这里寻找的通常被称为“知识约会”。

在这种情况下，你的主键将是你的常规键加上知识开始日期。

你的结束日期可能为空表示当前记录或者是一个“时间结束”的标志。

在更新时，你通常会将当前记录的结束日期设置为“现在”，并插入一个以相同“现在”开始的新记录，带有新值。

在“删除”时，你只需将结束日期设置为“现在”即可。

- Nathan Ernst

---

这不是一个坏的解决方案，但它会对性能产生什么影响呢？特别是当你想要获取当前记录时，这将给你的选择语句增加很多开销。 - Paul Sonier

Answer 3

1. 我已经完成了。

2.a) 版本号在某种程度上解决了唯一约束问题，尽管这只是放宽了唯一性，不是吗。

2.b) 您还可以将旧版本存档到另一个表中。

- Gidon Wise

Answer 4

以下是一些你需要考虑的额外问题：

1. 删除操作有多频繁？你的性能预算如何？这会影响你的选择。如果用户只删除单行记录（比如问答网站上的回答），与每小时从源中删除记录不同，那么你的设计答案也会不同。

2. 你将如何在系统中显示已删除的记录？仅通过管理目的还是任何用户都可以查看已删除的记录？这会有所不同，因为你可能需要根据用户来设计过滤机制。

3. 外键约束将如何工作？一个表是否可以引用另一个表中已删除的记录？

4. 当你添加或更改现有表时，已删除的记录会发生什么？

通常，非常关注审计的系统使用像Steve Prentice提到的表格。它通常具有原始表中的每个字段，所有约束都被关闭。它通常会有一个动作字段来跟踪更新与删除，并包括更改的日期/时间戳以及用户。

查看示例，请参考https://data.stackexchange.com/stackoverflow/query/new中的PostHistory表。

- Conrad Frix