"Employees (EmployeeId, FirstName, LastName, De"/>
热门：华为支持参数分辨率解决方法控制属性启动升级错误平台推荐步骤模式查询运行空间文件夹鼠标图标重启激活联想检查状态应用程序

关于sql：用于修订的数据库设计？

2023-04-18 17:55:44

Database Design for Revisions?

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

例如 对于"雇员"实体

设计1：

1
2
3
4
5
6
-- 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：

1
2
3
4
5
6
7
-- 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个实体的修订版)。

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

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

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

1
2
3
4
5
6
7
8
[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实用程序来根据表定义自动编写触发器。

只是一个想法！

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

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

    编辑

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

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

    • 永不更改的列或不需要其历史记录的列不应在历史记录表中进行跟踪，以免发生膨胀。即使可以从新旧值中导出数值，也可以为数字值存储增量值，从而使后续查询更加容易。

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

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

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

    1
    2
    3
    4
    5
    6
    7
    [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；一旦您需要例如回滚到记录的旧版本-使用管理员控制台自动或"手动"回滚，它就不太方便了。

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

    笔记

    • 记录表仅包含最新修订版本；
    • 历史记录表包含"记录"表中记录的所有先前修订；
    • 历史记录表的主键是带有RevisionId列的Records表的主键；
    • 考虑其他辅助字段，例如ModifiedBy-创建特定修订版的用户。您可能还需要一个字段DeletedBy来跟踪谁删除了特定修订版。
    • 考虑DateModified的含义-要么意味着创建此特定修订版本的位置，要么意味着何时将该特定修订版本替换为另一个修订版本。前者要求该字段位于"记录"表中，乍一看似乎更直观。但是，第二种解决方案似乎对于删除的记录(删除此特定修订版的日期)更为实用。如果您选择第一个解决方案，则可能需要第二个字段DateDeleted(当然，仅在需要时)。取决于您以及您实际想要记录的内容。

    设计2中的操作非常简单：

    修改

    • 将记录从"记录"表复制到"历史记录"表，为其提供新的RevisionId(如果在"记录"表中尚不存在)，处理DateModified(取决于您如何解释它，请参见上面的注释)
    • 继续正常更新记录表中的记录

    删除

    • 与"修改"操作的第一步完全相同。根据您选择的解释，相应地处理DateModified / DateDeleted。

    取消删除(或回滚)

    • 从"历史记录"表中进行最高(或某些特定？)修订并将其复制到"记录"表中

    列出特定记录的修订历史

    • 从"历史记录"表和"记录"表中选择
    • 想想您对该操作的确切期望；它可能会确定您需要从DateModified / DateDeleted字段中获取哪些信息(请参见上面的注释)

    如果您选择设计2，那么执行该操作所需的所有SQL命令将非常容易，而且易于维护！也许，如果您同时在Records表中使用辅助列(RevisionId，DateModified)，则将使两个表保持完全相同的结构(唯一键除外)会容易得多！这将允许使用简单的SQL命令，这些命令将容忍任何数据结构更改：

    1
    INSERT INTO EmployeeHistory SELECT * FROM Employe WHERE ID = XX

    不要忘记使用交易！

    至于扩展，此解决方案非常有效，因为您无需来回转换XML中的任何数据，只需复制整个表行-非常简单的查询，使用索引-非常有效！

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

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

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

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

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

    IsActive是最新版本的标志

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

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

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

    请注意，您应允许主键非唯一。

    我过去看过的方法是

    1
    Employees (EmployeeId, DateModified, < Employee FIELDS > , BOOLEAN isCurrent );

    您永远不会在此表上"更新"(更改isCurrent的有效值除外)，只需插入新行即可。对于任何给定的EmployeeId，只有1行可以具有isCurrent == 1。

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

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

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

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

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

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

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

    enter image description here

    在此示例中，我们有一个名为employee的实体。 user表保存用户的记录，而entity和entity_revision是两个表，用于保存系统中所有实体类型的修订历史记录。这种设计的工作方式如下：

    实体ID和修订ID的两个字段

    系统中的每个实体都有其自己的唯一实体ID。您的实体可能会经过修订，但其entity_id将保持不变。您需要将此实体ID保留在employee表中(作为外键)。您还应该将实体的类型存储在实体表中(例如'employee')。现在，对于revision_id，正如其名称所示，它可以跟踪您的实体修订。我为此找到的最佳方法是使用employee_id作为您的version_id。这意味着您将为不同类型的实体使用重复的修订版ID，但这对我来说没有好处(我不确定您的情况)。唯一需要注意的是，entity_id和version_id的组合应该是唯一的。

    在entity_revision表中还有一个状态字段，用于指示修订状态。它可以具有以下三种状态之一：latest，obsolete或deleted(不依赖于修订日期可以极大地提高查询效率)。

    关于revision_id的最后一点说明，我没有创建将employee_id连接到revision_id的外键，因为我们不想为将来可能添加的每种实体类型更改entity_revision表。

    INSERTION

    对于要插入数据库中的每个员工，您还将添加一条记录到entity和entity_revision。最后两个记录将帮助您跟踪记录的插入者和插入时间。

    UPDATE

    现有员工记录的每次更新将实现为两个插入，一个在employee表中，一个在entity_revision中。第二个将帮助您了解更新记录的人和时间。

    缺失

    为了删除员工，将一条记录插入到entry_revision中，说明删除和完成。

    如您在该设计中看到的，没有数据会从数据库中更改或删除，更重要的是，每种实体类型仅需要一个表。我个人认为该设计非常灵活并且易于使用。但是我不确定您的需求，因为您的需求可能有所不同。

    [UPDATE]

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

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

    我会尝试第二种方法。您可以通过只有一个雇员表和一个DateModified字段来简化此过程。 EmployeeId + DateModified将是主键，您可以通过添加一行来存储新修订。这样，归档旧版本和从存档还原版本也更加容易。

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

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

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

    怎么样：

    • 员工ID
    • DateModified

      • 和/或修订号，具体取决于您要如何跟踪
    • ModifiedByUSerId

      • 加上您要跟踪的其他任何信息
    • 员工领域

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

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

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

    1
    2
    3
    4
    5
    // Holds Employee Entity
    "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

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

    或全球解决方案：

    1
    2
    3
    4
    5
    // Holds Employee Entity
    "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

    // Holds ALL entities revisions IN ROWS.
    "EntityChanges (EntityName, EntityId, DateModified, OldValue, NewValue, FieldName)"

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

    1
    2
    3
    4
    5
    // Holds Employee Entity
    "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

    // Holds ALL entities revisions IN ROWS.
    "EntityChanges (EntityName, EntityId, DateModified, XMLChanges)"

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

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

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

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

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

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

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

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

    用于数据库设计实体历史记录

    推荐阅读