审计表:维护参考完整性 - 好坏 [英] Audit Tables: Maintaining Referential Integrity - Good or Bad

查看:198
本文介绍了审计表:维护参考完整性 - 好坏的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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.

在设计讨论过程中提出的一个要点是使StudentID和CourseID在StudentHistory表中成为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.

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

关键参数的任何细节(ex performance,best practice,设计灵活性等)将被高度赞赏。

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 ,您希望不要失去一个事实,即当他们现在有95%的时候,学生原来有65%的这个事实。这个审计跟踪可以让你回头看看变化,看看发生了什么事,以及是谁做的。从中可以确定特定用户滥用系统的功能。在某些方面,这可能是一种备份,因为您可以将这些更改回滚到之前的状态,而不会退回整个表。

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆