如何版本控制数据库中的记录 [英] How to version control a record in a database

问题描述

假设我在数据库中有一条记录，管理员和普通用户都可以进行更新.

Let's say that I have a record in the database and that both admin and normal users can do updates.

谁能建议一个好的方法/架构来控制这个表中的每个更改，以便可以将记录回滚到以前的版本?

Can anyone suggest a good approach/architecture on how to version control every change in this table so it's possible to roll back a record to a previous revision?

推荐答案

假设您有一个管理员和用户可以更新的 FOO 表.大多数情况下，您可以针对 FOO 表编写查询.快乐的日子.

Let's say you have a FOO table that admins and users can update. Most of the time you can write queries against the FOO table. Happy days.

然后，我将创建一个 FOO_HISTORY 表.这包含 FOO 表的所有列.主键与 FOO 相同，外加一个 RevisionNumber 列.从 FOO_HISTORY 到 FOO 有一个外键.您还可以添加与修订相关的列，例如 UserId 和 RevisionDate.以不断增加的方式在所有 *_HISTORY 表中填充 RevisionNumbers(即来自 Oracle 序列或等价物).不要依赖一秒钟内只有一个更改(即不要将 RevisionDate 放入主键中).

Then, I would create a FOO_HISTORY table. This has all the columns of the FOO table. The primary key is the same as FOO plus a RevisionNumber column. There is a foreign key from FOO_HISTORY to FOO. You might also add columns related to the revision such as the UserId and RevisionDate. Populate the RevisionNumbers in an ever-increasing fashion across all the *_HISTORY tables (i.e. from an Oracle sequence or equivalent). Do not rely on there only being one change in a second (i.e. do not put RevisionDate into the primary key).

现在，每次更新 FOO 时，就在更新之前，将旧值插入 FOO_HISTORY.您可以在设计中的某个基本级别执行此操作，以免程序员意外错过这一步.

Now, every time you update FOO, just before you do the update you insert the old values into FOO_HISTORY. You do this at some fundamental level in your design so that programmers can't accidentally miss this step.

如果您想从 FOO 中删除一行，您有一些选择.要么级联并删除所有历史记录，要么通过将 FOO 标记为已删除来执行逻辑删除.

If you want to delete a row from FOO you have some choices. Either cascade and delete all the history, or perform a logical delete by flagging FOO as deleted.

当您对当前值非常感兴趣并且只是偶尔对历史感兴趣时，此解决方案是很好的.如果您总是需要历史记录，那么您可以输入有效的开始和结束日期，并将所有记录保存在 FOO 本身中.然后每个查询都需要检查这些日期.

This solution is good when you are largely interested in the current values and only occasionally in the history. If you always need the history then you can put effective start and end dates and keep all the records in FOO itself. Every query then needs to check those dates.

这篇关于如何版本控制数据库中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！