sql – 历史/可审计数据库

但是 – 当编辑或删除项目时,我需要存储旧数据;我需要能够看到更改前的数据.

数据库中还有不可编辑的项目,例如“读数”.他们真的是更多的日志.读数是针对传感器记录的,因为它是针对特定传感器的读数.

如果我生成一个读数报告,我需要能够看到一个位置或传感器的属性在阅读时.

基本上我应该可以重建任何时间点的数据.

现在,我已经做到了这一点,并通过将以下列添加到每个可编辑的表,使其运行良好：

valid_from
valid_to
edited_by

如果valid_to = 9999-12-31 23:59:59那就是当前的记录.如果valid_to等于valid_from,则记录将被删除.

然而,我从来没有满足我需要用来强制执行外键一致性的触发器.

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

我想知道如果有另一种方式.

我看到人们提到使用特殊的历史表,但我不太喜欢维护几乎每一个表的2个表的想法(尽管它仍然是可能的).

也许我可以减少我的初始实现,不要麻烦检查不是“当前”的记录的一致性,也就是说只需要检查valid_to是9999-12-31 23:59:59的记录的约束.最后,使用历史表的人似乎没有对这些表进行约束检查(同样的原因,你需要触发器).

有人有什么想法吗？

PS – 标题还提到可审计数据库.在前面提到的系统中,总是有edit_by字段.这样可以跟踪所有更改,以便我们总是看到谁改变了记录.不知道有多大的区别.

谢谢.

解决方法

好的,所以在我坐的地方(提供完全可审计的数据库,你的一个特殊要求)和你坐的位置之间存在差距：根据你的问题和意见.我们可能会在评论中作出解释.这是一个从头开始的立场.

>为了提供这个要求,根本不需要：触发器;大规模复制破坏诚信;等等
>这不是一个经典的Temporal要求,所以不需要“时期”的能力,但你可以.
> ValidFrom和ValidTo是一个规范化错误：ValidTo是容易导出的数据;任何行中的ValidTo都重复,在下一行的ValidFrom中;您有更新异常(当您更新一行中的一列时,您还必须更新下一行中的另一列);您必须使用“current”的虚拟值.

>所有不必要的,仅使用ValidFrom,并保持数据库清洁和纯5NF.
>注意事项是,如果PostgreSQL无法执行子查询,而不会丢入堆(ala Oracle),那么可以使用有效的.

All of these things are editable in the system by users,and deletable.

好吧,不行它是一个拥有重要信息的数据库;具有引用完整性,而不是暂存器,所以用户不能只是走上去,“删除”一些东西.它将与用户维护历史数据的要求相矛盾(在阅读;警报;确认;行动;下载)中.

>不允许级联删除.这些功能是非数据库,MS Access类型的复选框.对于真正的数据库,RI约束阻止有孩子的父母被删除.
>主键不能(不应该)改变.例如.用户名; LocationId; NetworkSlaveCode从不改变;记住,他们是仔细考虑的标识符. PK的一个特点是它们是稳定的.
>您可以添加新用户;您可以更改当前用户的名称;但是您无法删除在“下载,确认”和“操作”中具有条目的用户.

Basically if it’s editable then it has to be historical (so that excludes readings and alerts).

也不包括：下载;致谢;动作.

参考表：SensorType;警告类型;操作类型.

和新的历史表：它们被插入,但它们不能被更新或删除.

The problem I find with the isObselete flag is.. Say if you change the Location,the Sensor foreign key will now point to an obselete record,meaning you will have to duplicate every sensor record. This problem gets exponentially worse as the hierachy gets bigger.

>好的,现在你明白Sensor中的LocationId(FK)不会改变;没有大规模的重复等等？第一个没有问题(在那个愚蠢的书里有),在第二个地方变得更加恶化.
> IsObsolete不足以满足您的需求. (参见下文)
>任何实际行中的UpdatedDtm(阅读等)标识当时生效的父(FK至传感器)历史行(其AuditedDtm).
>完整的关系能力;声明性诚信完整性等
>维护IDEF1X,强标识符的关系概念…只有一个当前的父行(例如位置)
>历史记录中的行是在更改前的当前行的图像,在所述的AuditedDtm.当行更改时,当前行(非历史记录)显示最后一个“更新数据”.
> AuditedDtm显示任何给定键的全部系列的UpdatedDtms;因此我用它在时间上“分割”了真正的钥匙.

所需要的是每个可更改表的历史记录表.我已经提供了四个识别表的位置;传感器; NetworkSlave;和用户.

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

数据模型

链接到Sensor Data Model with History(第2页包含历史表和上下文).

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

回应评论

(1)我的第一个问题是具有历史数据的参照完整性,因为我不确定有没有,如果我不确定它是如何工作的.例如,在SensoryHistory中,如果您看到我的意思,可以添加一个记录,其中有一个UpdateDtm指示位置本身存在之前的日期时间.这是否实际上是一个我不能肯定的问题 – 强制执行这可能是最高的.

(你在另一个问题上提出了类似的问题.)可能你所经历的dbs实际上没有参照完整性;关系线在那里只是为了文件; 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不能被删除
> Sensor中不存在SensorId的SensorHistory不能插入
> SensorHistory中的行中的LocationId SensorNo不能被删除.

(1.1)所有列应具有规则和检查约束以约束其值的范围.除了所有INSERT / UPDATE / DELETE都是程序化的事实之外,在存储过程中,因此不会发生事故,人们不会走到数据库并对其运行命令(除了SELECTS).

一般我远离触发器.如果你正在使用存储过程,并且正常的权限,那么这个：

在SensoryHistory中,如果你看到我的意思,其中有一个UpdateDtm指示位置本身存在之前的日期时间

被阻止那么在Sensor本身之前插入一个UpdateDtm的SensorHistory.但是procs不是声明式规则.但是,如果你想要双重确定(我的意思是双重的,因为INSERTS都是通过一个proc,用户的直接命令),那么你必须使用一个触发器.对我来说,这是顶端的.

(2)如何表示删除？我可以添加一个标志给非历史版本的表,我猜.

尚未确定.例如.您是否接受传感器被删除时,它是最终的…(是的,维护历史)…然后当一个新的传感器被添加到位置,它将有一个新的传感器没有…没有传感器在逻辑上被新的传感器替代,有时间或没有间隙？

从最终用户的角度来看,通过软件,他们应该能够随意添加,编辑和删除传感器,而不限于此.但是,一旦删除,它被删除,不能被删除.没有什么可以阻止他们再次添加一个传感器,尽管具有完全相同的参数.

还可以“删除”位置,网络存储和用户.

好.那么具有相同参数的新传感器是真正的新型,它具有新的SensorNo,并且独立于任何以前的逻辑传感器.我们可以在四个识别表中添加一个IsObsolete BOOLEAN;现在被认为是足够的.删除现在是一个软删除.

(2.1)对于NetworkSensor和LoggerSensor,它们实际上依赖于两个父母：如果父母中的任何一方已经过时,它们已经过时了.所以没有一点给他们一个IsObsolete列,它具有双重含义,可以从适用的父级派生.

(2.2)为了清楚,用户不能从任何交易和历史表中删除任何行,对吗？

(3)更新表时,最新的方法是将新行插入到历史表中并更新主表？一个事务中正常的SQL语句呢？

是.这是一个交易的经典使用,根据ACID属性,它是Atomic;它或者成功地toto或toto失败(稍后在问题被修复时被重试).

(4)参考书

最终和最初的文本是时间数据和关系模型C J Date,H Darwen,N A Lorentzos.像我们那样,拥抱RM的人都熟悉这些扩展,以及RM的继承者需要什么？而不是其他一些方法.

参考书是可怕的,免费的. PDF不是PDF(无搜索;无索引).打开我的MS和Oracle告诉我们一些很好的东西在很多绒毛上面.许多失实陈述.不值得回应(如果你想要一个适当的审查,打开一个新的问题).

(4.1)ValidTo除ValidFrom之外.这本书的严重错误(在我的答案的顶部确定)然后费力解决.不要把错误放在第一位,你没有什么可以解决的.据了解,这将消除您的触发器.

(4.2)考虑到正常化和时间要求的简单规则.首先,您需要深入了解(a)时间要求和(b)DataTypes,正确的使用和限制.始终存储：

>即时作为DATETIME,例如UpdatedDtm
>间隔为INTEGER,清楚地标识列名中的单位,例如. IntervalSec
>期间.取决于结合或分离.

>适用于(4.1)适用的连接：使用一个DATETIME;期间的结束可以从下一行的期间的开始得出.
>对于不合规期,是的,您需要2 x DATETIME,例如RentedFrom和RentedTo,其间有间隙.

(4.3)他们混淆了“时间主键”,这使代码复杂化(除了要求触发器来控制更新异常).我已经交付了一个干净(经过测试)的时间主键.

(4.4)他们混淆了“现在”的虚拟值,非真实值和空值.我不允许在数据库中这样的东西.由于我没有存储重复的ValidTo,我没有问题,没有什么可以解决的.

(4.5)人们不得不想知道为什么528页的“教科书”在网络上可用,PDF格式不好.

(5)我[用户]可以安静地删除所有的LocationHistory行,例如(仅在Location表中只留下当前版本) – 即使可能存在概念上“属于”先前版本的SensorHistory行位置,如果这是有道理的.

对我来说没有意义,我们必须关闭的沟通中仍然存在差距.请保持互动直到关闭.

>在实际(标准的ISO / IEC / ANSI SQL)数据库中,我们不会向用户授予GRANT INSERT / UPDATE / DELETE权限.我们仅授予选择和参考(对于所选用户)所有INSERT / UPDATE / DELETE都在事务中进行编码,这意味着存储过程.然后,我们将GRUST EXEC对每个存储的proc进行选定的用户(使用ROLES来减少管理).

>因此,没有人可以从任何表中删除而不执行proc.
>不要写任何历史记录表中的proc来删除.这些行不应该被删除.在这种情况下,不允许和不存在代码是约束.
>从技术上来说,所有历史行都是有效的,没有时间关注自己.最旧的LocationHistory行在更改前包含原始位置行的before-image.最小的LocationHistory行是当前位置行的前图像.因此,中间的每个LocationHistory行都有效,并适用于中间的期间.
>不需要“修剪”或查找可以删除的几个LocationHistory行,因为它们适用于未使用的句点：它们都被使用. (最重要的是,不需要检查Location子句的任何映射到任何LocationHistory行,以证明它).
>底线：用户不能从任何历史记录(或事务)表中删除.
还是你的意思再次不一样？
注意我已经添加了(1.1).

(6)纠正DM中的一个错误.警报是阅读而不是传感器的表达.

(7)在另一个问题/答案中更正了“业务规则”,以反映出来;和这个问题中暴露的新规则.

(8)你明白/欣赏,因为我们有一个完全符合IDEF1X标准的模型,

>标识符通过整个数据库进行,保留其功能.例如.列出致谢时,可以直接与位置和传感器连接;中间的表不必被读取(如果使用Id键,则它们必须被读取).这就是为什么在关系数据库中需要少量连接的原因(以及在非标准化数据库中需要更多的连接).>只有当特定上下文相关时,才需要导航子类型等.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!