sql - 修订的数据库设计？

Question

我们在项目中要求将实体的所有修订(更改历史)存储在数据库中。目前我们有 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 个实体需要维护修订)。

Answer 1

我认为这里要问的关键问题是“谁/什么会使用历史记录”？

如果它主要用于报告/人类可读的历史，我们过去已经实现了这个方案......

创建一个名为“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字段来存储正在更新的表的关键字段的值。如果它是组合键，我们只需在字段之间使用“~”进行字符串连接。

我确信这个系统可能有缺点 - 对于大量更新的数据库，性能可能会受到影响，但对于我的网络应用程序，我们获得的读取次数多于写入次数，而且它似乎表现得很好。我们甚至编写了一个小的 VB.NET 实用程序来根据表定义自动编写触发器。

只是一个想法!