如何版本控制在一个数据库中的记录 [英] 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.

任何人都可以提出一个很好的方法/架构如何进行版本控制在此表中的每一个变化，因此可能回滚记录到previous版本。

Can anyone suggest a good approach/architecture how to version control every change in this table so it's possible to rollback 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 的外键。您还可以添加相关的修订，如用户ID和RevisionDate列。填充RevisionNumbers在跨所有 * _历史表（即从Oracle序列或同等）不断增加的方式。不要依赖于只存在于被第二个变化。 IE浏览器。不要把 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 (ie. from an Oracle sequence or equivalent). Do not rely on there only being one change in a second. ie. do not put RevisionDate into the primary key.

现在，每次你更新 FOO ，你做的更新之前你插入旧值插入 FOO_HISTORY 。你在设计中做到这一点在一些基本的水平，使程序员不能错过意外这一步。

Now, everytime 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 accidently 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 intself。然后每个查询都需要检查这些日期。

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 intself. Every query then needs to check those dates.

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