保持参照完整性——好还是坏? [英] Maintaining Referential Integrity - Good or Bad?

问题描述

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

We are planning on introducing simple Audit Trail in our database using triggers and separate history table for each table that requires auditing.

以表 StudentScore 为例，它很少有外键(例如 StudentID、CourseID)将其链接到相应的父表(Student & Course).

For example consider table StudentScore, it has few foreign keys (eg. StudentID, CourseID) linking it to corresponding parent tables (Student & Course).

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

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

If StudentScore requires auditing, we are planning to create audit table StudentScoreHistory -

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

如果 StudentScore 中的任何行被修改，我们会将旧行移动到 StudentScoreHistory.

If any row in StudentScore is modified we'll move old row to StudentScoreHistory.

在设计讨论期间提出的观点之一是将 StudentHistory 表中的 StudentID 和 CourseID 设为 FK，以保持参照完整性.支持这一点的论点是因为我们总是主要执行软(逻辑布尔标志)删除而不是硬删除，这有助于保持参照完整性以确保我们在审计表中没有任何孤立的 id.

One of the points raised during design discussion was to make StudentID and CourseID in StudentHistory table a FK, to maintain referential integrity. Argument made in favour of this was as we always mostly do a soft (logical Boolean flag) delete rather than hard delete, its good to maintain referential integrity to ensure we do not have any orphan ids in audit table.

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

这对我来说似乎有点奇怪.我同意@Jonathan Leffler 的评论，审计记录不应停止删除父数据.相反，如果需要，应通过主表中的外键而不是审计表中的外键进行处理.我想征求您的意见，以确保我不会遗漏将外键扩展到审计表的某些价值.

This seems to be a bit odd design to me. I do agree with @Jonathan Leffler's comment that audit record should not stop the deletion of parent data. Instead, if this is required, should be handled via foreign keys in main table and not in audit table. I want to get your opinion, to make sure I'm not missing some value in extending foreign keys to audit tables.

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

任何有关关键参数(例如性能、最佳实践、设计灵活性等)的详细信息都将受到高度赞赏.

Any details on key arguments (e.x. performance, best practice, design flexibility etc) would be highly appreciated.

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

For benefit of anyone looking for specific purpose and our environment:

目的:

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

环境:

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

推荐答案

在讨论审计时，我会回到它背后的目的.它并不是真正的备份，而是已经发生的事情的历史.例如，对于 StudentScore，您要确保不会丢失学生最初拥有 65% 而现在拥有 95% 的事实.此审计跟踪将允许您回顾更改以查看发生了什么以及是谁做的.由此，您可以确定特定用户为滥用系统做了什么.在某些方面，这可能是一种备份，因为您可以将这些更改回滚到它们以前的状态，而无需回滚整个表.

When discussing auditing, I would go back to the purpose behind it. It isn't really a backup but rather a history of what has been. For example, for StudentScore, you would want to be sure not to lose the fact that the student originally had a 65% when they now have a 95%. This audit trail would allow you to walk back through the changes to see what happened and who did it. From this, you could identify what a particular user did to abuse the system. In some ways this could be a type of backup since you could roll back these changes to their previous states without rolling back entire tables.

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

With this in mind (if my assumptions about what you are using this for are correct), the only place you would want a FK/PK relationship is between the history table and its "live" counterpart. Your audit (history) table should not refer to any other table because it is no longer a part of that system. Instead, it is simply a record of what has happened in one table. Period. The only referential integrity you might want to consider is between the history table and the live table (thus the possible FK/PK relationship). If you allow records to be deleted from the live table, don't include the FK in the history table. Then the history table could include deleted records (which is what you want if you allow deletions).

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

Don't get confused with relational integrity in the main database with this history table. The history tables are all stand-alone. They only serve as a history of one table (not a set of tables).

可以将两个历史表关联在一起，实时表和历史表之间的关系甚至更高级(例如，同时包含实时和历史的学生和课程)，因此您甚至可以处理学生被删除的可能性(不寒而栗)因为该记录仍将在历史表中.这里唯一的问题是如果您不保留特定表的历史记录，在这种情况下您选择丢失该数据(如果您允许删除).

The relating of two history tables together is possible and even more advanced relations between the live and history tables together (Students and Courses with both live and history, for example) so you can deal with even the possibility that a student was deleted (shudder) since the record would still be in the history table. The only issue here would be if you don't keep the history for a particular table, in which case you are choosing to lose that data (if you allow deletes).

这篇关于保持参照完整性——好还是坏?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！