sql - 维护参照完整性-好还是坏？

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

例如考虑表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，以保持参照完整性。支持这一点的理由是，我们通常总是进行软(逻辑 bool(boolean) 标志)删除而不是硬删除，这有利于维护参照完整性，以确保我们在审核表中没有任何孤立的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's comment，即审核记录不应停止删除父级数据。相反，如果需要，则应通过主表而非审核表中的外键进行处理。我想征求您的意见，以确保在将外键扩展到审计表时不会丢失任何值(value)。

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

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

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

目的:

维护关键数据历史记录

允许对用户 Activity 进行审核，并支持重新创建方案

在有限程度上允许回滚用户 Activity

环境:

事务数据库

并非每个表都需要审核

尽可能使用软删除，特别是用于静态/引用数据

很少有高度事务化的表使用硬删除

最佳答案

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

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

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

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

关于sql - 维护参照完整性-好还是坏？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/5794417/