维护参照完整性-好还是坏？ [英] 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中的任何行是修改后，我们将旧行移至Stude ntScoreHistory。

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. 在一定程度上允许回滚用户活动

环境：

• 事务数据库


• 并非每个表都需要审核


• 使用软尽可能地删除，特别是针对静态/引用数据


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

• Transactional database
• Not every table requires auditing
• Uses soft-delete to the extent possible, specifically for static/reference data
• Few highly transactional tables do use hard deletes

推荐答案

在讨论审计时，我将回到其背后的目的。它实际上不是备份，而是历史记录。例如，对于 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屋！