数据库设计用于修订? ？

我们在项目中要求存储数据库中实体的所有修订(更改历史记录)。目前，我们为此提出了两项建议:

例如「雇员」实体

设计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个实体需要维护修订版)。

43555 次浏览
小开

我以前见过的方法是

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

永远不要在这个表上“更新”(除了更改 isCurrent 的有效性) ，只需要插入新行。对于任何给定的 EmployeeId，只有1行可以有 isCurrent = = 1。

维护它的复杂性可以通过视图和“而不是”触发器来隐藏(在 Oracle 中，我假设其他 RDBMS 也有类似的情况) ，如果表太大，无法通过索引处理，您甚至可以转到物化视图)。

这种方法没有问题，但是最终可能会产生一些复杂的查询。

就个人而言，我非常喜欢你的设计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”已经在。

最好的方法是在每条记录上都有一个“ is current”字段，并且(可能)有一个时间戳或 FK 到一个日期/时间表。

然后，插入必须设置“ is current”，并取消前一条“ is current”记录上的“ is current”设置。查询必须指定“ is current”，除非您想要所有的历史记录。

如果它是一个非常大的表，或者预期会有大量的修订，那么还需要对它进行进一步的调整，但是这是一个相当标准的方法。

小开

如果希望执行第一个操作，也可能希望对 Employestable 使用 XML。大多数较新的数据库允许查询 XML 字段，所以这并不总是一个问题。不管是最新版本还是早期版本，使用一种方法访问员工数据可能更简单。

不过我会试试第二种方法。您可以通过只有一个包含 DateAmendment 字段的 Employestable 来简化此操作。EmployeeId + DateAmendment 将是主键，您可以通过添加一行来存储新的修订。这样，归档旧版本和从归档中恢复版本也更容易。

另一种方法可能是由丹林斯泰特的 数据库模型。我曾经为荷兰统计局做过一个项目，使用了这个模型，效果非常好。但是我不认为它对日常数据库的使用有直接的用处。不过你可能会从他的论文中得到一些想法。

小开

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

Employee (Id, Name, ... , 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列的表。这样，您只需要存储已更改的确切列，而不必存储整个行。

小开
最佳答案
  1. 执行 没有将所有内容放在一个具有 IsCurrent 鉴别器属性的表中。这只会导致以后的问题，需要代理键和其他各种各样的问题。
  2. 设计2在模式更改方面确实存在问题。如果更改了 EmployeeHistory 表，则必须更改 EmployeeHistory 表以及与之相关的所有流程。模式更改工作可能会加倍。
  3. 设计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)。但是，请记住，这种痛苦的经历表明，单个审计表将成为今后的一个巨大瓶颈; 为所有审计表创建单个审计表可能是值得的。

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

小开

数据库程序员 blog 中的 历史记录表文章可能很有用——涵盖了这里提到的一些要点，并讨论了 delta 的存储。

剪辑

在 历史记录表的文章中，作者(肯尼斯 · 唐斯)建议维护一个至少有7列的历史表:

  1. 变化的时间戳,
  2. 进行更改的用户,
  3. 标识已更改的记录的标记(其中历史记录与当前状态分开维护) ,
  4. 更改是插入、更新还是删除,
  5. 旧的价值,
  6. 新的价值,
  7. 增量(用于更改数值)。

不应该在历史记录表中跟踪永远不更改或不需要其历史记录的列，以避免膨胀。为数值存储 delta 可以使后续查询更容易，即使它可以从新旧值派生出来。

历史表必须是安全的，防止非系统用户插入、更新或删除行。应该只支持定期清除以减少总体大小(如果用例允许的话)。

小开

这个怎么样:

  • 雇员证
  • 修改日期
    • 和/或修订号，这取决于您希望如何跟踪它
  • 修改
    • 还有其他你想追踪的信息
  • 员工领域

您创建主键(EmployeeId，DateAmendment) ，为了获得“当前”记录，您只需为每个 EmployeeId 选择 MAX (DateAmendment)。存储 IsCurrent 是一个非常糟糕的主意，因为首先，它可以计算，其次，它太容易使数据失去同步。

您还可以创建一个仅列出最新记录的视图，并且大多数情况下在应用程序中使用该视图。这种方法的好处是不需要重复的数据，也不需要从两个不同的地方收集数据(当前位于 EmployeesHistory 中，存档在 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 存储比这种触发器使用的本机数据库表存储效率低得多。

小开

避免使用设计1; 一旦您需要回滚到记录的旧版本(自动或“手动”使用管理员控制台) ，它就不太方便。

我真的没有看到设计2的缺点。我认为第二个，History 表应该包含第一个，Records 表中的所有列。例如，在 mysql 中，您可以轻松地创建具有与另一个表(create table X like Y)相同结构的表。而且，当您准备更改实时数据库中 Records 表的结构时，无论如何都必须使用 alter table命令——而且对于 History 表也不需要花很大的力气来运行这些命令。

笔记

  • 记录表只包含最新的修订;
  • “历史记录”表包含“记录”表中所有以前修订的记录;
  • 历史表的主键是添加了 RevisionId列的 Records 表的主键;
  • 考虑其他辅助字段，如 ModifiedBy-创建特定修订的用户。您可能还希望有一个字段 DeletedBy来跟踪谁删除了特定的修订。
  • 想一想 DateModified应该是什么意思——要么它意味着这个特定的修订版是在哪里创建的，要么它意味着这个特定的修订版被另一个修订版取代的时候。前者要求字段位于 Records 表中，并且第一眼看上去似乎更直观; 而第二种解决方案似乎更适用于已删除的记录(此特定修订被删除的日期)。如果使用第一个解决方案，则可能需要第二个字段 DateDeleted(当然，只有在需要的情况下)。这取决于你和你真正想要记录的东西。

设计2中的操作非常琐碎:

修改
  • 将记录从 Records 表复制到 History 表，赋予它新的 RevisionId (如果尚未出现在 Records 表中) ，处理 DateAmendment (取决于您如何解释它，参见上面的说明)
  • 继续正常更新唱片表中的记录
删除
  • 与修改操作的第一步完全相同。根据您选择的解释，相应地处理 DateAmendment/DateDelete。
取消删除(或回滚)
  • 从 History 表中获取最高的(或某个特定的?)修订版，并将其复制到 Records 表中
列出特定记录的修订历史
  • 从历史表和记录表中选择
  • 思考一下您到底希望从这个操作中得到什么; 它可能会决定您需要从 DateAmendment/DateDelete 字段中得到什么信息(参见上面的注释)

如果使用设计2，那么所有需要执行的 SQL 命令都将非常非常简单，并且维护起来也非常容易！也许，它会更容易 如果在 Records 表中也使用辅助列(ABC0、 DateModified) ，则可以使两个表保持完全相同的结构(除了独特的键) ！这将允许使用简单的 SQL 命令，该命令可以容忍任何数据结构的更改:

insert into EmployeeHistory select * from Employe where ID = XX

别忘了使用事务处理！

至于缩放 ，这个解决方案非常有效，因为您不需要来回转换任何来自 XML 的数据，只需要复制整个表行——非常简单的查询，使用索引——非常有效！

小开

我将与你们分享我的设计，它不同于你们两个的设计，因为它需要每个实体类型一个表。我发现描述任何数据库设计的最好方法是通过 ERD，这是我的:

enter image description here

在这个例子中，我们有一个名为 雇员的实体。使用者表保存用户的记录，实体和 实体 _ 修订是两个表，它们保存系统中所有实体类型的修订历史。下面是这个设计的工作原理:

实体 _ id和 (修订 _ id)两个字段

系统中的每个实体都有自己的唯一实体 ID。您的实体可能会经过修订，但是它的实体 _ id 将保持不变。您需要在您的雇员表中保留这个实体 id (作为外键)。您还应该将实体的类型存储在 实体表中(例如，“ em Employee”)。现在，对于 Amendment _ id，如其名称所示，它跟踪您的实体修订。我找到的解决这个问题的最佳方法是使用 雇员 ID作为修订 _ id。这意味着对于不同类型的实体，您将拥有重复的修订 id，但是这对我来说不是一件好事(我不确定您的情况)。需要注意的唯一一点是，实体 _ id 和修订 _ id 的组合应该是唯一的。

实体 _ 修订表中还有一个 国家字段，表示修订的状态。它可以有三种状态之一: latest、 obsolete或 deleted(不依赖于修订日期可以大大提高查询效率)。

最后要注意的一点是，我没有创建一个外键，用于将 employee _ id 连接到 Amendment _ id，因为我们不希望为将来可能添加的每个实体类型更改 tity _ Amendment 表。

插入

对于要插入到数据库中的每个 雇员，还要向 实体和 实体 _ 修订添加一条记录。最后两条记录将帮助您跟踪记录是由谁以及何时插入到数据库中的。

更新

现有雇员记录的每个更新都将实现为两个插入，一个插入在雇员表中，另一个插入在 tity _ Amendment 中。第二个选项将帮助您了解记录是由谁更新的以及何时更新的。

删除

为了删除一个雇员，一个记录被插入到 tity _ Amendment 中，声明删除和完成。

正如您可以看到的，在这种设计中，没有数据被更改或从数据库中删除，更重要的是，每个实体类型只需要一个表。就我个人而言，我发现这种设计真的很灵活，很容易与工作。但我不确定你是否适合，因为你的需求可能有所不同。

[更新]

在新的 MySQL 版本中支持分区，我相信我的设计也具有最好的性能之一。可以使用 type字段对 entity表进行分区，而使用 state字段对 entity_revision进行分区。这将大大增强 SELECT查询，同时保持设计简洁。


提交答案