保持参考完整性 - 好还是坏？

我们计划在数据库中引入简单的Audit Trail,使用触发器和每个需要审计的表的单独历史表.

例如,考虑表StudentScore,它具有很少的外键(例如,StudentID,CourseID)将其链接到相应的父表(学生和课程).

Table StudentScore (
    StudentScoreID, -- PK
    StudentID ref Student(StudentID),  -- FK to Student
    CourseID ref Course(CourseID),   -- FK to Course
)

如果StudentScore需要审核,我们计划创建审核表StudentScoreHistory -

Table StudentScoreHistory (
    StudentScoreHistoryID, -- PK
    StudentScoreID,
    StudentID,
    CourseID,
    AuditActionCode,
    AuditDateTime,
    AuditActionUserID
)

如果StudentScore中的任何行被修改,我们会将旧行移至StudentScoreHistory.

在设计讨论期间提出的一点是在StudentHistory表中将StudentID和CourseID设为FK,以保持参照完整性.支持这一点的论据是因为我们总是主要做一个软(逻辑布尔标志)删除而不是硬删除,它有利于维护引用完整性以确保我们在审计表中没有任何孤立ID.

Table StudentScoreHistory (
    StudentScoreHistoryID, -- PK
    StudentScoreID,
    StudentID ref Student(StudentID), -- FK to Student
    CourseID ref Course(CourseID), -- FK to Course
    AuditActionCode,
    AuditDateTime,
    AuditActionUserID
)

这对我来说似乎有点奇怪.我同意@Jonathan Leffler的评论,即审计记录不应该停止删除父数据.相反,如果需要,则应通过主表中的外键处理,而不是在审计表中处理.我想得到你的意见,以确保我没有错过将外键扩展到审计表的一些价值.

现在我的问题是: 在历史表中使用这些外键是一个好的设计吗？

关键参数的任何细节(性能,最佳实践,设计灵活性等)将受到高度赞赏.

为了寻找特定目的和环境的人的利益:

目的:

维护关键数据历史记录

允许审核用户活动并支持重新创建方案

在有限的范围内允许回滚用户活动

环境:

交易数据库

并非每个表都需要审核

尽可能使用软删除,特别是静态/参考数据

很少有高度事务性的表确实使用硬删除

IAmTimCorey.. 25

在讨论审计时,我会回到它背后的目的.它不是真正的备份,而是历史.例如,对于StudentScore,您可能希望确保不会失去这样一个事实:当学生现在拥有95%时,他们最初有65%.此审计跟踪将允许您返回更改以查看发生了什么以及由谁完成.由此,您可以确定特定用户滥用系统的行为.在某些方面,这可能是一种备份类型,因为您可以将这些更改回滚到以前的状态而不回滚整个表.

考虑到这一点(如果我对你使用它的假设是正确的),你想要FK/PK关系的唯一地方就是历史表和它的"实时"对应物.您的审计(历史)表不应引用任何其他表,因为它不再是该系统的一部分.相反,它只是一个表中发生的事情的记录.期.您可能要考虑的唯一参照完整性是历史表和实时表(因此可能的FK/PK关系).如果允许从实时表中删除记录,请不要在历史记录表中包含FK.然后历史记录表可以包含已删除的记录(如果允许删除,则为您所需的记录).

不要与主历史记录表中的主数据库中的关系完整性混淆.历史表都是独立的.它们仅用作一个表的历史(不是一组表).

将两个历史表相关联在一起是可能的,甚至更高级的现场和历史表之间的关系(例如,有生活和历史的学生和课程),所以你甚至可以处理学生被删除的可能性(颤抖)因为记录仍然在历史表中.这里唯一的问题是如果你不保留特定表的历史记录,在这种情况下你选择丢失该数据(如果你允许删除).

在讨论审计时,我会回到它背后的目的.它不是真正的备份,而是历史.例如,对于StudentScore,您可能希望确保不会失去这样一个事实:当学生现在拥有95%时,他们最初有65%.此审计跟踪将允许您返回更改以查看发生了什么以及由谁完成.由此,您可以确定特定用户滥用系统的行为.在某些方面,这可能是一种备份类型,因为您可以将这些更改回滚到以前的状态而不回滚整个表.

考虑到这一点(如果我对你使用它的假设是正确的),你想要FK/PK关系的唯一地方就是历史表和它的"实时"对应物.您的审计(历史)表不应引用任何其他表,因为它不再是该系统的一部分.相反,它只是一个表中发生的事情的记录.期.您可能要考虑的唯一参照完整性是历史表和实时表(因此可能的FK/PK关系).如果允许从实时表中删除记录,请不要在历史记录表中包含FK.然后历史记录表可以包含已删除的记录(如果允许删除,则为您所需的记录).

不要与主历史记录表中的主数据库中的关系完整性混淆.历史表都是独立的.它们仅用作一个表的历史(不是一组表).

将两个历史表相关联在一起是可能的,甚至更高级的现场和历史表之间的关系(例如,有生活和历史的学生和课程),所以你甚至可以处理学生被删除的可能性(颤抖)因为记录仍然在历史表中.这里唯一的问题是如果你不保留特定表的历史记录,在这种情况下你选择丢失该数据(如果你允许删除).

我建议不要将外键扩展到审计表.我的建议是将审计中的数据扩展到外键值.

它不是将CourseID存储为"1",而是"HTML4".这样,如果删除了外键值,则审计表仍然有效.如果外键值在将来的任何时间从"HTML4"更改为"HTML5",这也将成立.如果您只存储了外键,那么您将告诉审核员以前的学生做了"HTML5",这是不正确的.

另一个很大的好处是能够将审计跟踪发送到另一台服务器进行数据挖掘,而不会出现任何问题.

我已经使用了上面的设置一段时间了,它对我有用.

如果您需要重新创建场景,那么我会说是的,您需要FK,并且拥有它们我认为这将是一种更容易的方式来跟踪相关的相关详细记录.但是,这会导致删除问题,以及可能在主键表中更改的信息.在这种情况下,我会说你不想要删除具有FKS在其他表中的记录,而是用软删除,你已经表示.

至于PK表中的信息变化,请注意.设置FK将是获得一些追溯能力的简单方法,但它并不完美.有权衡.要获得绝对完美的历史记录,您基本上需要创建所有相关记录的备份副本,只要审计候选记录在其上发生某些事情.您需要确定适当的粒度级别并与之一致,因为完整的事件记录可能很难设置,并且在此过程中占用了大量空间.

此外,这可能是也可能不是您的选择,但我会强烈考虑ApexSQL Audit + ApexSQL Log等工具的组合,而不是本土的审计解决方案.根据您的需求,这两个工具与定期归档您的事务日志相结合将涵盖您需要执行的操作.审计工具可以将数据存储在同一个数据库或其他地方,日志工具可以有选择地恢复数据.只是一个想法.