我们在项目中要求将实体的所有修订(更改历史记录)存储在数据库中。目前，我们有2个为此设计的提案：

例如对于"雇员"实体

设计1：

-- Holds Employee Entity
"Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

-- Holds the Employee Revisions in Xml. The RevisionXML will contain
-- all data of that particular EmployeeId
"EmployeeHistories (EmployeeId, DateModified, RevisionXML)"

设计2：

-- Holds Employee Entity
"Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

-- In this approach we have basically duplicated all the fields on Employees 
-- in the EmployeeHistories and storing the revision data.
"EmployeeHistories (EmployeeId, RevisionId, DateModified, FirstName, 
      LastName, DepartmentId, .., ..)"

还有其他方法可以做到这一点吗？

"设计1"的问题在于，每次需要访问数据时，我们都必须解析XML。这将减慢该过程的速度，并增加一些限制，例如我们无法在修订数据字段上添加联接。

"设计2"的问题在于，我们必须在所有实体上重复每个字段(我们要维护其大约70-80个实体的修订版)。

解决方案

回答

我过去看过的方法是

Employees (EmployeeId, DateModified, < Employee Fields > , boolean isCurrent );

我们永远不会在此表上"更新"(除非更改isCurrent的有效值)，而只是插入新行。对于任何给定的EmployeeId，只有1行可以具有isCurrent == 1.

维护视图的复杂性可以由视图和"代替"触发器来隐藏(在oracle中，我认为其他RDBMS具有类似的功能)，如果表太大并且无法由索引处理，我们甚至可以进入实例化视图) 。

这种方法是可以的，但是我们可能会遇到一些复杂的查询。

就个人而言，我非常喜欢Design 2的实现方式，这也是我过去所做的方式。它易于理解，易于实现且易于维护。

它还为数据库和应用程序创建了很少的开销，尤其是在执行读取查询时，这很可能会在99％的时间内完成。

自动创建历史记录表和要维护的触发器也非常容易(假设它将通过触发器完成)。

回答

我们有类似的要求，我们发现通常用户只是想查看已更改的内容，而不必回滚任何更改。

我不确定用例是什么，但是我们要做的是创建和审核表，该表会随着业务实体的更改而自动更新，包括任何外键引用和枚举的友好名称。

每当用户保存更改时，我们都会重新加载旧对象，进行比较，记录更改并保存实体(所有操作均在单个数据库事务中完成，以防出现任何问题)。

这似乎对我们的用户非常有效，并且使我们不必为拥有与业务实体相同字段的完全独立的审计表而烦恼。

回答

我认为这里要问的关键问题是"谁/将使用什么历史"？

如果主要用于报告/人类可读的历史记录，那么我们过去已经实现了此方案...

创建一个名为" AuditTrail"的表或者具有以下字段的表...

[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[EventDate] [datetime] NOT NULL,
[TableName] [varchar](50) NOT NULL,
[RecordID] [varchar](20) NOT NULL,
[FieldName] [varchar](50) NULL,
[OldValue] [varchar](5000) NULL,
[NewValue] [varchar](5000) NULL

然后，我们可以在所有表​​中添加" LastUpdatedByUserID"列，该列应在每次对表进行更新/插入时进行设置。

然后，我们可以向每个表添加触发器，以捕获发生的任何插入/更新，并在此表中为每个更改的字段创建一个条目。由于该表还为每个更新/插入提供了" LastUpdateByUserID"，因此我们可以在触发器中访问该值，并在添加到审计表时使用它。

我们使用RecordID字段存储要更新的表的键字段的值。如果是组合键，我们只需在字段之间使用"〜"进行字符串连接。

我敢肯定，对于大量更新的数据库，该系统可能存在缺点，可能会影响性能，但是对于我的Web应用程序，读取的次数多于写入的次数，并且似乎表现良好。我们甚至编写了一个小的VB.NET实用程序来根据表定义自动编写触发器。

只是一个想法！

回答

听起来我们想跟踪一段时间内对特定实体的更改，例如ID 3，" bob"，" 123 main street"，然后是另一个ID 3，" bob"，" 234 elm st"，依此类推，从本质上讲，它可以吐出修订历史记录，显示每个地址" bob"都位于。

最好的方法是在每个记录上都有一个"当前"字段，并(可能)在日期/时间表上加上时间戳或者FK。

然后，插入程序必须设置"当前"，并且还要在上一个"当前"记录上取消设置"当前"。查询必须指定"当前"，除非我们需要所有历史记录。

如果它是一个很大的表，或者需要大量的修订，则需要对此做进一步的调整，但这是一个相当标准的方法。

回答

如果要进行第一个操作，则可能也要对Employees表使用XML。大多数较新的数据库都允许我们查询XML字段，因此这并不总是问题。无论是最新版本还是早期版本，拥有一种访问员工数据的方法都可能会更简单。

我会尝试第二种方法。我们可以通过仅使用一个带有DateModified字段的Employees表来简化此操作。 EmployeeId + DateModified将是主键，我们可以通过添加一行来存储新修订。通过这种方式存档较旧的版本并从存档还原版本也更加容易。

做到这一点的另一种方法可能是Dan Linstedt的数据仓库模型。我为荷兰统计局做了一个使用此模型的项目，并且效果很好。但是我认为这对于日常数据库使用没有直接的帮助。我们可能会从阅读他的论文中得到一些想法。

回答

Ramesh，我参与了基于第一种方法的系统开发。
事实证明，将修订版本存储为XML会导致数据库的巨大增长，并显着降低速度。
我的方法是每个实体有一张表：

Employee (Id, Name, ... , IsActive)

IsActive是最新版本的标志

如果要将某些其他信息与修订相关联，则可以创建单独的表
包含该信息，并使用PK \ FK关系将其与实体表链接。

这样，我们可以将所有版本的员工存储在一个表中。
这种方法的优点：

• 简单的数据库结构
• 由于表变为仅追加，因此没有冲突
• 我们只需更改IsActive标志即可回滚到以前的版本
• 无需连接即可获取对象历史记录

请注意，我们应该允许主键是非唯一的。

回答

我们已经实现了与Chris Roberts建议的解决方案非常相似的解决方案，并且对我们来说效果很好。

唯一的区别是我们仅存储新值。毕竟旧值存储在上一个历史记录行中

[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[EventDate] [datetime] NOT NULL,
[TableName] [varchar](50) NOT NULL,
[RecordID] [varchar](20) NOT NULL,
[FieldName] [varchar](50) NULL,
[NewValue] [varchar](5000) NULL

假设我们有一个包含20列的表格。这样，我们只需要存储已更改的确切列，而不必存储整个行。

回答

• 不要将所有内容都放在具有IsCurrent鉴别符属性的表中。这只会导致一系列问题，需要代理密钥和各种其他问题。
• 设计2确实存在模式更改问题。如果更改了Employees表，则必须更改EmployeeHistories表以及该表随附的所有相关存储。可能使架构更改工作加倍。
• 设计1运作良好，如果做得好，不会对性能造成太大影响。我们可以使用xml模式甚至索引来克服可能的性能问题。我们对解析xml的评论是有效的，但是我们可以使用xquery轻松创建视图-我们可以将其包含在查询中并加入其中。像这样的东西

CREATE VIEW EmployeeHistory
AS
, FirstName, , DepartmentId

SELECT EmployeeId, RevisionXML.value('(/employee/FirstName)[1]', 'varchar(50)') AS FirstName,

  RevisionXML.value('(/employee/LastName)[1]', 'varchar(100)') AS LastName,

  RevisionXML.value('(/employee/DepartmentId)[1]', 'integer') AS DepartmentId,

FROM EmployeeHistories

回答

如果确实需要审计跟踪，那么我会倾向于审计表解决方案(完成对其他表上重要列的非规范化副本的复制，例如，" UserName")。但是请记住，这种痛苦的经历表明，一个审计表将是一个巨大的瓶颈。为所有审核表创建单独的审核表可能是值得的。

如果我们需要跟踪实际的历史(和/或者将来)版本，则标准解决方案是使用开始，结束和持续时间值的某种组合来跟踪具有多行的同一实体。我们可以使用视图来方便地访问当前值。如果采用这种方法，则版本化数据引用的是可变但未版本控制的数据，则可能会遇到问题。

回答

Database Programmer博客中的" History Tables"文章可能很有用，涵盖了此处提出的一些观点并讨论了增量的存储。

编辑

在"历史记录表"文章中，作者(肯尼思·唐斯)建议保持至少七列的历史记录表：

• 变更时间戳记
• 进行更改的用户，
• 令牌，用于标识已更改的记录(其中，历史记录与当前状态分开维护)，
• 无论更改是插入，更新还是删除，
• 旧的价值，
• 新价值
• 增量(用于更改数值)。

永不更改的列或者不需要其历史记录的列不应在历史记录表中进行跟踪，以免发生膨胀。即使可以从新旧值中导出数值，也可以通过存储数值的增量值来简化后续查询。

历史记录表必须是安全的，并且禁止非系统用户插入，更新或者删除行。仅应支持定期清除以减小整体大小(如果用例允许，则应这样做)。

回答

怎么样：

• 员工ID
• 员工领域

我们创建主键(EmployeeId，DateModified)，并获得"当前"记录，我们只需为每个employeeid选择MAX(DateModified)。存储IsCurrent是一个非常糟糕的主意，因为首先可以对其进行计算，其次，它非常容易使数据不同步。

我们还可以创建仅列出最新记录的视图，并在应用程序中使用该视图时主要使用该视图。这种方法的好处是我们无需重复数据，也不必从两个不同的位置(Employees中为当前，EmployeesHistory中为存档)收集数据以获取所有历史记录或者回滚等) 。

回答

数据修订是时间数据库的"有效时间"概念的一个方面。对此进行了大量研究，并且出现了许多模式和指南。对于那些感兴趣的人，我写了很长的回复，并提供了许多有关此问题的参考。

回答

如果要依靠历史数据(出于报告原因)，则应使用类似以下的结构：

// Holds Employee Entity
"Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

// Holds the Employee revisions in rows.
"EmployeeHistories (HistoryId, EmployeeId, DateModified, OldValue, NewValue, FieldName)"

或者适用于全球的解决方案：

// Holds Employee Entity
"Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

// Holds all entities revisions in rows.
"EntityChanges (EntityName, EntityId, DateModified, OldValue, NewValue, FieldName)"

我们也可以将修订版本保存为XML，那么一个修订版本只有一条记录。这看起来像：

// Holds Employee Entity
"Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

// Holds all entities revisions in rows.
"EntityChanges (EntityName, EntityId, DateModified, XMLChanges)"

回答

如果必须存储历史记录，请创建一个影子表，该影子表具有与要跟踪的表相同的架构，并且具有"修订日期"和"修订类型"列(例如，"删除"，"更新")。编写(或者生成见下文)一组触发器以填充审计表。

制作一个可以读取表的系统数据字典并生成脚本的工具非常简单，该脚本创建影子表和一组触发器以填充影子表。

不要尝试为此使用XML，XML存储的效率比这种触发器使用的本机数据库表存储要低得多。