sql – 历史/可审计数据库

valid_from
valid_to
edited_by

1月11日修订

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

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

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

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

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

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

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

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

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

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

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

数据模型

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

不熟悉关系建模标准的读者@R_523_9381@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,正确的使用和限制.始终存储：

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

>适用于(4.1)适用的连接：使用一个datetiR_906_11845@E;期间的结束可以从下一行的期间的开始得出.
>对于不合规期,是的,您需要2 x datetiR_906_11845@E,例如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键,则它们必须被读取).这就是为什么在关系数据库中需要少量连接的原因(以及在非标准化数据库中需要更多的连接).>只有当特定上下文相关时,才需要导航子类型等.