sql - 历史/可审核的数据库

这个问题与可以在其他问题之一中找到的模式有关here.基本上在数据库中，我存储用户，位置，传感器等。所有这些内容都可以由用户在系统中编辑，并且可以删除。

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

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

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

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

现在，我之前已完成此操作，并通过向每个可编辑表中添加以下列来使其正常工作:

valid_from
valid_to
edited_by

如果valid_to = 9999-12-31 23:59:59则为当前记录。如果valid_to等于valid_from，则删除记录。

但是，我对强制使用外键一致性所需的触发器感到不满意。

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

我想知道是否还有另一种方法。

我见过有人提到使用特殊的历史表，但是我真的不喜欢几乎每1个表维护2个表的想法(尽管这仍然是可能的)。

也许我可以减少我的初始实现，以便不费心检查不是“当前”记录的一致性-即，仅费心检查对valid_to为9999-12-31 23:59:59的记录的约束。毕竟，使用历史记录表的人似乎对这些表没有约束检查(出于相同的原因，您将需要触发器)。

有人对此有任何想法吗？

PS-标题还提到可审核的数据库。在前面提到的系统中，始终有edited_by字段。这样可以跟踪所有更改，因此我们始终可以看到谁更改了记录。不知道有什么不同。

谢谢。

最佳答案

修订日期:2011年1月1日

好的，所以我的位置(提供完全可审核的数据库；您的要求是其中的特殊要求)与您的位置之间存在差距:根据您的问题和评论。我们可能会在评论中进行讨论。这是一个起点。

要提供此要求，根本不需要:触发器；大量重复；完整性受损；等

这也不是经典的时间要求，因此不需要“时段”功能，但是可以。

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

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

需要说明的是，如果PostgreSQL无法执行子查询而不落入一个堆(ala Oracle)，则可以，请保留ValidTo。

好吧，不。它是一个保存重要信息的数据库；拥有参照完整性，而不是便签本，因此用户不能随便走过去并“删除”某些内容。它将与维护历史数据的相同用户要求(在阅读，警报，确认，操作，下载中)相矛盾。

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

主键不能(不应)更改。例如。用户身份; LocationId; NetworkSlaveCode永不更改；请记住，它们被仔细考虑为标识符。 PK的一个特征是它们很稳定。

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

还不包括:下载；致谢；动作。

和参考表:SensorType； AlertType;动作类型。

以及新的“历史记录”表:它们已插入其中，但是无法更新或删除。

好吧，现在您了解LocationId中的Sensor(FK)不会改变；有没有大量重复，等等？首先没有问题(这本愚蠢的书中有!)，而在第二地方则成倍恶化。

IsObsolete不足以满足您的要求。 (请参阅下面)

任何实际行(UpdatedDtm等)中的Reading标识当时有效的父级(FK到Sensor)历史记录行(其AuditedDtm)。

完全关系能力；声明性参照完整性等

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

历史记录中的行是指定行AuditedDtm之前当前行的图像(在更改之前)。当前行(非历史记录)显示了更改行时的最后一个UpdatedDtm。

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

每个可变表仅需要一个历史表。我为四个标识表提供了“历史记录”表:传感器;网络从站；和用户。

请阅读以了解Auditable in the accounting sense。

资料模型

链接到Sensor Data Model with History(第2页包含“历史记录”表和上下文)。

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

对评论的回应

(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)
    ...

这些声明的约束由服务器强制执行；不通过触发器；不在应用代码中。这意味着:

不能插入Sensor中不存在的LocationId的Location

不能删除LocationId中有行的Location中的Sensor

不能插入SensorHistory中不存在的LocationId+SensorNo的Sensor

不能删除LocationId+SensorNo中包含行的Sensor。

(1.1)所有列都应具有RULE和CHECK约束，以约束其值的范围。除了所有INSERT / UPDATE / DELETE在存储的proc中都是程序化的事实之外，因此不会发生意外，人们不会走到数据库并对它运行命令(SELECTS除外)。

通常，我远离触发器。如果您使用的是存储过程和普通权限，则此操作:

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

被阻止。因此，在SensorHistory之前插入具有UpdatedDtm的SensorHistory也是如此。但是proc不是声明性规则。但是，如果您想加倍确定(并且我的意思是加倍，因为INSERTS都是通过proc，用户直接命令完成的)，那么一定要使用触发器。对我来说，那是最重要的。

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

尚未确定。例如。您是否接受删除SensorHistory的操作，它是最终的...(是，保留历史记录)...然后将新的Sensor添加到Sensor时，它将具有一个新的Location ...没有在逻辑上用新的SensorNo替换Sensor，是否有时间间隔？

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

以及“删除” Locations, NetworkSlaves和Users。

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

(2.1)对于NetworkSensor和LoggerSensor，它们实际上取决于两个 parent :如果 parent 中的任何一个已过时，则它们已过时。因此，给他们一个IsObsolete列毫无意义，该列具有双重含义，可以从适用的父级派生。

(2.2)请注意，用户不能从任何“事务和历史记录”表中删除任何行，对吗？

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

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

(4)参考书

权威性文本是时间数据和关系模型C J Date，H Darwen，NA Lorentzos。例如，我们中那些接受RM的人都熟悉扩展，以及RM后续产品的要求。而不是其他方法。

参考书是可怕的，而且是免费的。 PDF不是PDF(无搜索；无索引)。打开我的MS和Oracle会告诉您；一些好东西散布在许多绒毛中。许多错误陈述。不值得详细回答(如果您需要适当的评论，请打开一个新问题)。

(4.1)ValidTo以及ValidFrom。这本书所犯的严重错误(在我的答案的顶部已经确定)；然后努力解决。首先不要犯错误，而第二要解决的事情也没有。据我了解，这将消除您的触发器。

(4.2)简单的规则，同时考虑到归一化和时间要求。首先，您需要深入了解(a)时间要求和(b)数据类型，正确的用法和限制。始终存储:

即时为DATETIME，例如。已更新Dtm

间隔为INTEGER，在列名中清楚地标识Unit，例如。 IntervalSec

期。取决于合取或不合取。

对于连词，此要求为(4.1):使用一个DATETIME；周期的结尾可以从下一行的周期的开头得出。

对于分离时间段，是的，您需要2 x DATETIME，例如RentedFrom和RentedTo，两者之间有间隙。

(4.3)它们使“临时主键”混乱，使代码复杂化(除了需要触发器来控制更新异常外)。我已经提供了一个干净的(经过测试的)临时主键。

(4.4)它们与伪值，非实值和“Now”的Null混淆。我不允许在数据库中使用这种东西。由于我没有存储重复的ValidTo，所以我没有问题，没有什么要解决的。

(4.5)人们不得不怀疑为什么网上有528页的“教科书”以PDF格式的劣势免费提供。

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

对我而言，这没有意义，我们之间仍需弥合差距。请继续互动直到关闭。

在真实(标准ISO / IEC / ANSI SQL)数据库中，我们对用户执行而不是 GRANT INSERT / UPDATE / DELETE权限。我们仅向SELECT用户授予 SELECT和REFERENCES (向所选用户)所有INSERT / UPDATE / DELETE均编码在Transactions中，这意味着存储的proc。然后，我们将每个存储的proc上的EXEC授予选定的用户(使用ROLES减少管理)。

因此，如果不执行proc，没有人可以从任何表中删除。

不要编写要从任何“历史记录”表中删除的proc。这些行不应删除。在这种情况下，代码的不允许和不存在就是约束。

从技术上讲，所有“历史记录”行均有效，因此您无需担心任何期间。最旧的LocationHistory行包含更改前原始Location行的前镜像。最年轻的LocationHistory行是当前Location行的前镜像。因此，中间的每个LocationHistory行均有效，并适用于中间的Period。

无需“修剪”或查找一些LocationHistory行，可以根据它们应用于未使用的Period来删除这些行:它们全部已使用。 (最终，无需检查Location子项到任何LocationHistory行的任何映射，就可以证明这一点。)

底线:用户无法从任何“历史记录”(或“交易”)表中删除。

还是您再次表示不同的意思？

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

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

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

(8)您是否理解/赞赏，因为我们拥有完全兼容IDEF1X的模型，因此请标识符:

标识符贯穿整个数据库，并保持其功能。例如。列出Acknowledgements时，可以将它们直接与Location和Sensor连接；中间的表不必读取(如果使用了Id键，则必须为该表)。这就是为什么事实上在关系数据库中需要较少的联接(而在非规范化数据库中需要更多的联接)的原因。

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

关于sql - 历史/可审核的数据库，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/4491173/