历史/可审计数据库

Question

这个问题与我的其他问题之一中的架构相关 此处基本上在我的数据库中，我存储用户、位置、传感器等。所有这些东西都可以由用户在系统中编辑和删除。

但是 - 当编辑或删除项目时，我需要存储旧数据；我需要能够看到更改之前的数据是什么。

数据库中还有不可编辑的项目，例如“读数”。它们实际上更像是一根木头。读数是根据传感器记录的，因为它是特定传感器的读数。

如果我生成读数报告，我需要能够查看读取时位置或传感器的属性。。

基本上我应该能够重建任何时间点的数据。

现在，我之前已经完成了此操作，并通过向每个可编辑表添加以下列来使其运行良好：

valid_from
valid_to
edited_by

如果 valid_to = 9999-12-31 23:59:59 那么这就是当前记录。如果 valid_to 等于 valid_from，则删除该记录。

然而，我对用于强制外键一致性所需的触发器一直不满意。

我可以通过使用“PostgreSQL”数据库的扩展来避免触发器。这提供了一个名为“period”的列类型，它允许您存储两个日期之间的时间段，然后允许您执行 CHECK 约束以防止时间段重叠。这或许是一个答案。

我想知道是否还有其他方法。

我见过人们提到使用特殊的历史表，但我真的不喜欢为几乎每 1 个表维护 2 个表的想法（尽管它仍然可能是一种可能性）。

也许我可以减少最初的实现，不再费心检查非“当前”记录的一致性，即只费心检查 valid_to 为 9999-12-31 23:59:59 的记录的约束。毕竟，使用历史表的人似乎没有对这些表进行约束检查（出于同样的原因，您需要触发器）。

有人对此有什么想法吗？

PS - 标题还提到了可审计数据库。在我之前提到的系统中，总是有edited_by字段。这允许跟踪所有更改，以便我们始终可以看到谁更改了记录。不确定这可能会产生多大的差异。

谢谢。

Answer 1

2011 年 1 月 1 日修订

好吧，我的立场（提供完全可审计的数据库；您的数据库是对此的特殊要求）和您的立场之间存在差距：基于您的问题和评论。我们可能会在评论中解决这个问题。这是一个可以开始的位置。

• 要满足此要求，根本不需要：触发器；大量重复；诚信被破坏；

• 要求，因此不需要“周期”功能，但您可以。

• ValidFrom 和 ValidTo 是一个规范化错误：ValidTo 是很容易导出的数据；任何行中的 ValidTo 都是重复的，在下一行的 ValidFrom 中；您有更新异常（当您更新一行中的一列时，您还必须更新下一行中的另一列）；您必须使用“当前”的虚拟值。

  • 所有不必要的，仅使用ValidFrom，并保持数据库干净和纯粹的5NF。

  • 需要注意的是，如果 PostgreSQL 无法在不陷入堆的情况下执行子查询（ala Oracle），那么很好，保留 ValidTo。

所有这些内容都可以由用户在系统中编辑和删除。

嗯，不。它是一个保存重要信息的数据库；具有引用完整性，而不是暂存器，因此用户不能直接走到它并“删除”某些内容。这将与用户维护历史数据的要求相矛盾（在读取；警报；确认；操作；下载）。

• 不允许级联删除。这些功能是非数据库、MS Access 类型的复选框。对于真实的数据库，RI 约束会阻止删除带有子项的父项。

• 主键不能（不应该）更改。例如。用户身份;位置ID； NetworkSlaveCode 永远不会改变；请记住，它们是经过仔细考虑的标识符。 PK的特点之一是稳定。

• 您可以添加新用户；您可以更改当前用户的姓名；但您不能删除在“下载”、“确认”、“操作”中有条目的用户。

基本上，如果它是可编辑的，那么它必须是历史的（以便排除读数和警报）。

还排除：下载；致谢；行动。

以及参考表：SensorType；警报类型；动作类型。

新的历史表：它们被插入，但不能更新或删除。

我发现 isObselete 标志的问题是..假设您更改位置，传感器外键现在将指向一个过时的记录，这意味着您必须复制每个传感器记录。随着层次结构变大，这个问题会呈指数级恶化。

• 好吧，现在你明白了 Sensor 中的 LocationId (FK) 不会改变；没有大量重复等？首先没有问题（在那本愚蠢的书里有！），第二个地方会变得指数级恶化。

• IsObsolete 不足以满足您的要求。（请参阅下文）

• 任何实际行（Reading 等）中的 UpdatedDtm 标识父级（FK 到 Sensor）当时有效的历史行（其 AuditedDtm）。

• 完整的关系能力；声明性引用完整性等

• 维护 IDEF1X，强标识符的关系概念 ... 只有一个当前父行（例如位置）

• 历史记录中的行是当前行的图像，在更改之前，位于指定的 AuditedDtm。当前行（非历史记录）显示行更改时的最后一个 UpdatedDtm。

• AuditedDtm 显示任何给定密钥的整个 UpdatedDtms 系列；因此我用它在时间意义上“分区”真实的密钥。

所需要的只是每个可更改表的历史表。我提供了四个识别表的历史记录表：位置；传感器;网络从属；和用户。

请阅读本文以了解会计意义上的可审计。

数据模型

链接到具有历史记录的传感器数据模型（第 2 页包含历史记录表和上下文）。

不熟悉关系建模标准的读者可能会发现 IDEF1X 表示法很有用。

对评论的回应

(1) 我的第一个问题是历史数据的参照完整性，因为我不确定是否存在，如果存在，我也不确定它是如何工作的。例如，在 SensoryHistory 中，可以添加一条包含 UpdatedDtm 的记录，指示位置本身存在之前的日期时间，如果您明白我的意思的话。我不确定这是否真的是一个问题 - 强制执行可能有点过分。

（您在另一个问题中提出了类似的问题。）您所经历的数据库可能实际上并没有参照完整性到位；关系线只是用于记录； RI 是“在应用程序代码中实现的”（这意味着没有 RI）。

这是一个 ISO/IEC/ANSI 标准 SQL 数据库。这允许声明性引用完整性。每个关系行都实现为 PK::FK 引用，即声明的实际约束。例如：

CREATE TABLE Location
    ...
    CONSTRAINT UC_PK
        PRIMARY KEY (LocationId)
    ...
CREATE TABLE Sensor
    ...
    CONSTRAINT UC_PK
        PRIMARY KEY (LocationId, SensorNo)
    CONSTRAINT Location_Sensor_fk
        FOREIGN KEY (LocationId)
        REEFERENCES Location(LocationId)
    ...
CREATE TABLE SensorHistory
    ...
    CONSTRAINT UC_PK
        PRIMARY KEY (LocationId, SensorNo, UpdatedDtm))
    CONSTRAINT Sensor_SensorHistory_fk
        FOREIGN KEY (LocationId, SensorNo)
        REEFERENCES Sensor (LocationId, SensorNo)
    ...

Those Declared Constraints are enforced by the server; not via triggers; not in app code. That means:

• 无法将 Location 中不存在的 LocationId 的 Sensor 插入
• Location< 中的 LocationId无法删除 Sensor 中包含行的
• SensorHistory 其 LocationId+SensorNo 在 Sensor
• Sensor 中的 LocationId+SensorNo，如果 SensorHistory 中有行，则无法删除。

(1.1) 所有列都应该有 RULE 和 CHECK 约束来约束它们的值范围。除了所有 INSERT/UPDATE/DELETE 都是编程式的，在存储过程中，因此不会发生意外，并且人们不会走到数据库并针对它运行命令（SELECT 除外）。

一般来说，我会远离触发器。如果您使用存储过程和普通权限，那么：

在 SensoryHistory 中，可以添加一条记录，其中包含 UpdatedDtm，指示位置本身存在之前的日期时间，如果您明白我的意思

被阻止。在传感器本身之前插入具有 UpdatedDtm 的 SensorHistory 也是如此。但过程不是声明性规则。但是，如果您想双重确定（我的意思是双重确定，因为插入都是通过用户直接命令的过程），那么当然，您必须使用触发器。对我来说，这太过分了。

(2)如何指示删除？我猜我可以在表的非历史版本中添加一个标志。

还不确定。例如。您是否接受当Sensor被删除时，它就是最终的......（是的，历史记录被保留）......然后当一个新的Sensor被添加到Location，它将有一个新的 SensorNo ...逻辑上没有任何 Sensor 被新传感器替换，无论是否存在间隙时间 ？

从最终用户的角度来看，他们应该能够通过软件不受限制地随意添加、编辑和删除传感器。但是，是的，一旦删除，它就会被删除，并且无法恢复。没有什么可以阻止他们稍后使用完全相同的参数重新添加传感器。

并“删除”位置、NetworkSlaves和用户。

好的。那么具有相同参数的新 Sensor 才是真正的新的，它有一个新的 SensorNo，并且独立于任何先前的逻辑 Sensor。我们可以向四个标识表添加一个 IsObsolete BOOLEAN；现在被认为是足够的。删除现在是软删除。

(2.1) 对于 NetworkSensor 和 LoggerSensor，它们实际上依赖于两个父级：如果其中一个父级已过时，它们就已过时。因此，没有必要为它们提供一个 IsObsolete 列，该列具有双重含义，可以从适用的父级派生。

(2.2) 需要明确的是，用户不能从任何事务和历史表中删除任何行，对吧？

(3) 更新表时，什么方法最好在历史表中插入新行并更新主表？也许只是事务中的普通 SQL 语句？

是的。这是事务的经典用法，根据 ACID 属性，它是原子的；它要么在 toto 中成功，要么在 toto 中失败（稍后在问题解决后重试）。

(4) 参考书籍

权威且具有影响力的文本是时间数据和关系模型 CJ Date、H Darwen、NA Lorentzos。例如，我们这些拥抱 RM 的人熟悉这些扩展，以及 RM 的后继者需要什么；而不是其他一些方法。

参考书很糟糕，而且是免费的。该 PDF 不是 PDF（没有搜索；没有索引）。打开我的 MS 和 Oracle 就能说明问题；一些好的部分隐藏在大量的绒毛中。许多误传。不值得详细回应（如果您想要正确的评论，请提出一个新问题）。

(4.1) 除了 ValidFrom 之外，还有 ValidTo。这本书犯了严重的错误（如我的答案顶部所指出的）；然后费力地解决。一开始不犯错误，二就没有什么可解决的。据我了解，这将消除你的触发因素。

(4.2) 简单规则，同时考虑标准化和时间要求。首先也是最重要的，您需要深入了解 (a) 时间要求和 (b) 数据类型、正确用法和限制。始终存储：

• 即时为 DATETIME，例如。 UpdatedDtm

• 间隔为INTEGER，清楚地标识列名称中的单位，例如。 IntervalSec

• 句点。取决于合取或分离。

  • 对于合取，即此要求，(4.1) 适用：使用一个 DATETIME；周期的结束可以从下一行的周期的开始得出。
  • 对于不连续的时间段，是的，您需要 2 个 DATETIME，例如 RentedFrom 和一个 RentedTo，其间有间隙。

(4.3) 它们扰乱了“临时主键”，这使代码变得复杂（除了需要触发器来控制更新异常之外）。我已经交付了一个干净的（经过尝试和测试的）临时主键。

(4.4) 它们混淆了虚拟值、非实值和“现在”的空值。我不允许在数据库中出现这样的事情。由于我没有存储重复的 ValidTo，因此我没有问题，也没有什么可以解决的。

(4.5) 人们不得不想知道为什么一本 528 页的“教科书”可以在网上免费获得，而且 PDF 格式很差。

(5) 例如，我 [用户] 可以安静地愉快地删除所有 LocationHistory 行（仅在位置表中保留当前版本） - 即使可能存在概念上“属于”先前版本的 SensorHistory 行位置的版本，如果这有意义的话。

这对我来说没有意义，我们之间的沟通仍然存在差距，必须弥合。请继续互动直至关闭。

• 在真实的（标准 ISO/IEC/ANSI SQL）数据库中，我们不会向用户授予 INSERT/UPDATE/DELETE 权限。我们仅授予 SELECT 和 REFERENCES（授予选定的用户）所有 INSERT/UPDATE/DELETE 都在事务中编码，这意味着存储过程。然后我们将每个存储过程的执行权限授予选定的用户（使用角色来减少管理）。

  • 因此，没有人可以在不执行过程的情况下从任何表中删除。

  • 不要编写从任何历史表中删除的过程。这些行不应被删除。在这种情况下，不允许和不存在代码就是约束。

  • 从技术上讲，所有历史记录行都是有效的，没有需要担心的期间。最旧的 LocationHistory 行包含原始位置行更改之前的前像。最年轻的 LocationHistory 行是当前位置行的前像。因此，中间的每个 LocationHistory 行都是有效的，并且适用于中间的时间段。

  • 无需“修剪”或查找一些可以删除的 LocationHistory 行，因为这些行适用于未使用的期间：它们都已使用。 （当然，无需检查 Location 子级到任何 LocationHistory 行的任何映射即可证明这一点。）

  • 底线：用户无法从任何历史记录（或交易）表中删除。

  • 或者你的意思又是不同的吗？

  • 请注意，我已在上面添加了 (1.1)。

(6) 修正了DM中的一处错误。 Alert 是 Reading 的表达式，而不是 Sensor 的表达式。

(7) 更正了其他问题/答案中的业务规则以反映这一点；以及这个问题中暴露的新规则。

(8) 您是否理解/欣赏，由于我们拥有完全兼容 IDEF1X 的模型，因此关于标识符：

• 标识符贯穿整个数据库，保留其功能。例如。列出Acknowledgements时，可以直接与Location和Sensor连接；不必读取中间的表（如果使用 Id 键，则必须读取）。这就是为什么关系数据库中实际上需要较少的联接（而非规范化数据库中需要更多的联接）。

• 仅当特定上下文相关时才需要导航子类型等。

Answer 2

我以前也遇到过这种情况。根据您想要跟踪的数据量，这可能会令人望而生畏。历史表有时非常易于使用，因为您可以拍摄历史表中记录的“快照”，然后根据需要在生产表中进行更改。实施起来非常简单，但是根据您拥有的数据量及其更改频率，您最终可能会得到非常大的历史表。

另一种选择是记录所有更改，允许某人“重播”发生的事情并进行跟踪。每个更改都会记录到一个表或一个字段中（取决于您的需要），以跟踪谁、何时以及什么内容被更改为什么，即 2010 年 12 月 31 日，Bob 将状态从“开放”更改为“已关闭”。

您想要使用哪个系统通常取决于您稍后需要如何保留/查看/使用数据。自动报告、人工审核、两者的某种组合等。

Answer 3

根据您的预算和/或环境，您可能需要考虑使用 Oracle 的闪回归档功能。

您可以打开表中行的自动“归档”，然后使用

SELECT *
FROM important_data
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' DAY)

Oracle 之类的东西在基表上运行语句，负责在单独的（影子）表中维护历史记录。您可以对任何表执行此操作，以便还可以使用联接执行查询。