永不删除关系数据库架构设计

I am considering designing a relational DB schema for a DB that never actually deletes anything (sets a deleted flag or something).

我正在考虑为从不实际删除任何内容的数据库设计关系数据库模式(设置已删除的标志或其他内容)。

1) What metadata columns are typically used to accomodate such an architecture? Obviously a boolean flag for IsDeleted can be set. Or maybe just a timestamp in a Deleted column works better, or possibly both. I'm not sure which method will cause me more problems in the long run.

1)通常使用哪些元数据列来容纳这样的架构?显然,可以设置IsDeleted的布尔标志。或者只是删除列中的时间戳可以更好地工作,或者两者都可以。从长远来看,我不确定哪种方法会给我带来更多问题。

2) How are updates typically handled in such architectures? If you mark the old value as deleted and insert a new one, you will run into PK unique constraint issues (e.g. if you have PK column id, then the new row must have the same id as the one you just marked as invalid, or else all of your foreign keys in other tables for that id will be rendered useless).

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

4 个解决方案

#1

Here are some additional questions that you'll also want to consider

以下是您还需要考虑的其他一些问题

How often do deletes occur. What's your performance budget like? This can affect your choices. The answer to your design will be different depending of if a user deleting a single row (like lets say an answer on a Q&A site vs deleting records on an hourly basis from a feed)

删除的频率如何。你的绩效预算是什么样的?这可能会影响您的选择。您的设计答案会有所不同,具体取决于用户是否删除了一行(比如让我们在问答网站上说答案,而不是每小时从Feed中删除记录)
How are you going to expose the deleted records in your system. Is it only through administrative purposes or can any user see deleted records. This makes a difference because you'll probably need to come up with a filtering mechanism depending on the user.

您将如何在系统中公开已删除的记录。是仅通过管理目的还是任何用户都可以看到已删除的记录。这有所不同,因为您可能需要根据用户提出过滤机制。
How will foreign key constraints work. Can one table reference another table where there's a deleted record?

外键约束将如何工作。一个表可以引用另一个表,其中有一个已删除的记录吗?
When you add or alter existing tables what happens to the deleted records?

添加或更改现有表时,删除的记录会发生什么?

Typically the systems that care a lot about audit use tables as Steve Prentice mentioned. It often has every field from the original table with all the constraints turned off. It often will have a action field to track updates vs deletes, and include a date/timestamp of the change along with the user.

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

For an example see the PostHistory Table at http://data.stackexchange.com/*/query/new

有关示例,请参阅http://data.stackexchange.com/*/query/new上的PostHistory表

#2

If your goal is auditing, I'd create a shadow table for each table you have. Add some triggers that get fired on update and delete and insert a copy of the row into the shadow table.

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

#3

I think what you're looking for here is typically referred to as "knowledge dating".

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

In this case, your primary key would be your regular key plus the knowledge start date.

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

Your end date might either be null for a current record or an "end of time" sentinel.

对于当前记录或“结束时间”哨兵,您的结束日期可能为空。

On an update, you'd typically set the end date of the current record to "now" and insert a new record the starts at the same "now" with the new values.

在更新时,您通常会将当前记录的结束日期设置为“now”,并使用新值在同一“now”处插入新记录。

On a "delete", you'd just set the end date to "now".

在“删除”上,您只需将结束日期设置为“现在”。

#4

i've done that.

我做到了。

2.a) version number solves the unique constraint issue somewhat although that's really just relaxing the uniqueness isn't it.

2.a)版本号解决了一些独特的约束问题,虽然这真的只是放松了它的独特性。

2.b) you can also archive the old versions into another table.

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

#1