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

Question

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

例如考虑表StudentScore，它只有很少的外键（例如StudentID，CourseID）将其链接到相应的父表（Student＆Course）。

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的评论
，即审核记录不应阻止删除父数据。相反，如果需要，则应通过主表中的外键而不是审计表中的外键进行处理。我想征求您的意见，以确保在将外键扩展到审计表时不会丢失任何价值。

现在我的问题是： 在历史记录表中包含这些外键是否是一个好的设计？

我们将不胜感激有关关键论点的任何细节（例如性能，最佳实践，设计灵活性等）。

为了任何寻求特定目的和我们环境的人的利益：

目的：

1. 维护关键数据历史
2. 允许审核用户活动并支持重新创建方案
3. 在一定程度上允许回滚用户活动

环境：

• 交易数据库
• 并非每个表都需要审核
• 尽可能使用软删除，特别是用于静态/参考数据
• 很少有高度事务性的表使用硬删除

Answer 1

在讨论审计时，我将回到其背后的目的。它实际上不是备份，而是历史记录。例如，对于StudentScore，您可能要确保不要丢失一个事实，即学生原来有65％的学生现在有95％的事实。通过该审核跟踪，您可以回顾所做的更改，以了解发生了什么以及由谁执行。由此，您可以确定特定用户为滥用系统所做的事情。在某些方面，这可能是一种备份，因为您可以将这些更改回滚到以前的状态，而无需回滚整个表。

考虑到这一点（如果我对您将其用于什么的假设是正确的），则您唯一希望建立FK /
PK关系的位置是历史记录表及其“实时”对应项之间。您的审核（历史）表不应引用任何其他表，因为它不再是该系统的一部分。相反，它只是一张表中发生的事情的记录。时期。您可能要考虑的唯一参照完整性是在历史表和实时表之间（因此可能的FK
/ PK关系）。如果允许从活动表中删除记录，则不要在历史记录表中包括FK。然后，历史记录表可能包含已删除的记录（如果允许删除，这就是您想要的记录）。

使用此历史记录表，不要混淆主数据库中的关系完整性。历史记录表都是独立的。它们仅用作一个表（而不是一组表）的历史记录。

可以将两个历史记录表关联在一起，甚至可以将实时表和历史记录表之间的关系更高级（例如，具有实时和历史记录的学生和课程），因此您甚至可以处理删除学生的可能性（颤抖）因为记录仍将位于历史记录表中。唯一的问题是，如果您不保留特定表的历史记录，那么在这种情况下，您将选择丢失该数据（如果允许删除）。