数据库 - 数据版本控制 [英] Database - Data Versioning
问题描述
我已阅读SO的几个问题（例如这一个）对数据库中的数据进行版本控制。
I've read a few questions on SO (such as this one) in regards to versioning your data within a database.
我喜欢上面提到的一些建议。我有最长的时间想（需要）修改许多我的表，但从来没有得到它。作为一个程序员，只有简单的数据库工作在我的皮带下，我想知道如何真正地这样做。
I liked some of the suggestions that were mentioned. I have for the longest time wanted (needed) to revision many of my tables but never got around to it. Being a programmer with only simple database work under my belt I was wondering how one would actually go about doing this.
我不是要求在SQL语法的实际解决方案。我最终能够为自己找到（或者当时间到来时发布）。我只是要求人们评论他们将如何做，以及任何潜在的性能问题，如果我修订数以亿计的记录可能会有。
I'm not asking for the actual solution in SQL syntax. I can eventually figure that out for myself (or post SO when the time comes). I'm just asking for people to comment as how they would go about doing it and any potential performance problems there might be if I was to 'revision' hundreds of million of records. Or any other suggestions as long as it is based on the example below.
以一个简单的例子：
Person
------------------------------------------------
ID UINT NOT NULL,
PersonID UINT NOT NULL,
Name VARCHAR(200) NOT NULL,
DOB DATE NOT NULL,
Email VARCHAR(100) NOT NULL
Audit
------------------------------------------------
ID UINT NOT NULL,
UserID UINT NOT NULL, -- Who
TableName VARCHAR(50) NOT NULL, -- What
OldRecID UINT NOT NULL, -- Where
NewRecID UINT NOT NULL,
AffectedOn DATE NOT NULL, -- When
Comment VARCHAR(500) NOT NULL -- Why
我不知道如何将Audit表链接到任何其他
I'm not sure how one would link the Audit table to any other tables (such as Person) if the TableName is a string?
此外，假设我有三个GUI来填充：
Also, assuming that I have three GUI's to populate:
- 特定人物id的完整记录
- 列出所有人（按id）的表格视图
- 一个视图，显示每个人的修订信息在每个条目下方（每个人的修订版本，修订日期，修订评论等），按照最新版本排序。
要完成1和2，最好查询Person表或Audit表？
To accomplish 1 and 2, would it be better to query the Person table or the Audit table?
要完成3，一个所谓的数据库专家简单地获取所有记录并将其传递到软件进行处理，或按PersonID和受影响的日期分组？这通常是在一个查询或多个处理？
To accomplish 3, would a so called database expert simply get all records and pass it on to the software for processing, or group by PersonID and Affected date? Is this usually handled in one query or many?
推荐答案
我已经做了多年的各种审计计划，实现如下：
I have done various audit schemes over the years and I am currently going to implement something like this:
Person
------------------------------------------------
ID UINT NOT NULL,
PersonID UINT NOT NULL,
Name VARCHAR(200) NOT NULL,
DOB DATE NOT NULL,
Email VARCHAR(100) NOT NULL
Person_History
------------------------------------------------
ID UINT NOT NULL,
PersonID UINT NOT NULL,
Name VARCHAR(200) NOT NULL,
DOB DATE NOT NULL,
Email VARCHAR(100) NOT NULL
AuditID UINT NOT NULL
Audit
------------------------------------------------
ID UINT NOT NULL,
UserID UINT NOT NULL, -- Who
AffectedOn DATE NOT NULL, -- When
Comment VARCHAR(500) NOT NULL -- Why
当前记录总是在Person表中。如果有更改，将创建审核记录，并将旧记录复制到Person_History表中（注意，ID不会更改，并且可以有多个版本）
The current records are always in the Person table. If there is a change an audit record is created and the old record is copied into the Person_History table (note the ID does not change and there can be multiple versions)
审核ID在* _History表中，因此您可以将多个记录更改链接到一个审核记录。
The Audit ID is in the *_History tables so you can link multiple record changes to one audit record if you like.
编辑：
如果您对于每个基表都有一个单独的历史表，并且想要使用相同的表来保存旧的和已删除的记录，那么你必须用状态标志来标记记录。在查询当前记录时这是一个真正的痛苦的问题 - 相信我已经这样做了。
If you don't have a separate history table for each base table and want to use the same table to hold old and "deleted" records then you have to mark the records with a status flag. The problem with that it's a real pain when querying for current records - trust me I've done that.
这篇关于数据库 - 数据版本控制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
