database-design - 如何在事务数据库中存储已删除的行

Question

这是 heximal 在我的其他帖子中提出的评论的后续。我想要一个deleted_on列来检测十六进制表示该列是多余的已删除记录。

这是他的评论:

You're going to use deleted_xx fields to determine that the record is deleted?

imho, the best and most nice way is to add to record some activity attribute of boolean datatype (e.g. the field named ACTIVE). So in order to "delete" record, we must update the value of ACTIVE field and update_date, updated_by in single UPDATE query. In order to select all active records we just have to make query like:

SELECT * FROM MyTable WHERE ACTIVE=1

I know that Oracle Applications use such approach, and I agree

我还阅读了以下帖子:

Store deleted rows in a table

http://www.udidahan.com/2009/09/01/dont-delete-just-dont/

http://ayende.com/Blog/archive/2009/08/30/avoid-soft-deletes.aspx

我的问题是:如何在具有 的表上设置唯一约束活跃 上面的十六进制建议的标志。我的所有表中都有代理键。但我想确保自然键列(我们称之为业务键列)具有唯一约束。

如果我有 deleted_on字段来跟踪删除，然后我可以将此列作为自然键约束的一部分。因此，它允许具有相同业务组合键的多个已删除记录，仅在 deleted_on 日期字段中有所不同。

如果我有 isActive字段和使用 last_updated_on列来跟踪删除日期，我必须对自然键约束有 2 个选项

我可以包括 isActive作为我的自然键约束的一部分。但这将允许最多只删除一条具有相同业务组合键的已删除记录。

我可以包括 isActive加 last_updated_on作为自然键约束的一部分。但我看到有一个额外的列 deleted_on 使它更容易。

有什么想法吗？我在这里错过了什么吗？

Answer 1

“我的问题是如何在具有 isActive 标志的表上设置唯一约束，如上面的十六进制建议。我的所有表中都有代理键。但我想确保自然键列(我们称之为业务键列) 有一个唯一的约束。

如果我有一个 deleted_on 字段来跟踪删除，那么我可以将此列作为自然键约束的一部分。因此，它允许具有相同业务组合键的多个已删除记录，仅在 deleted_on 日期字段中有所不同。”

即使将 deleted_on DATE 字段作为自然键的一部分，您仍然无法在同一天全部删除-重新插入-删除。可能看起来很病态，但你真的能确定病态病例永远不会发生吗？

如果您的数据库需要反射(reflect)某些内容在与当前业务高度相关的意义上是“活跃”的事实，而其他一些内容是“不活跃”的，例如从某种意义上说，保留一段时间的唯一原因是出于存档目的，然后通过定义两个表来设计您的数据库以反射(reflect)这一事实:一个具有“事件”内容，另一个具有“存档”内容。每当删除发生时，也许您可​​以使用触发器自动实现“移动到存档”。

这样做，您可以让 DBMS 使用“事件”表上的唯一约束来强制执行自然键，并且可以在“归档”表中包含删除时间戳，甚至可能不需要在都在那张 table 上。