Maintaining Referential Integrity - Good or Bad?

2019-01-22 06:09发布

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

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
)

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

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

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

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
)

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.

Now my question is: Is it a good design to have these foreign keys in History 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:

Purpose:

  1. Maintain critical data history
  2. Allow auditing of user activity with support to recreate scenario
  3. To limited extent allow roll-back of user activity

Environment:

  • 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

9条回答
闹够了就滚
2楼-- · 2019-01-22 07:08

I wouldn't create a second set of tables for the 'audited' rows, just integrate your auditing functions into your existing production schema. It sounds like your purpose isn't backup and restore as of a given date/disaster, but trace history of changes per user or student, and that's a function of your application. I think your additional fields are fine, they just don't need to be added to another set of tables.

One problem with backup and restore processes is schema changes. Schemas tend to change over time which means you may not be able to restore directly from a backup. If you keep your auditing functions built into your production schema, you don't have to worry about breaking anything when you need to support additional functions.

查看更多
地球回转人心会变
3楼-- · 2019-01-22 07:09

If your system is really focused on transaction processing then my answer may not apply well to you, but in the datawarehouse/BI world, this problem is often solved by using a "star schema". In this approach, you would denormalize the important indicative information from the linked tables along with your audit records. This could include the PK values of the parent tables (i.e. the FK values on your audited table). However, you wouldn't preserve the actual referential integrity contstraints themselves.

So for your example, your StudentScoreHistory table could retain its StudentID column, without the FK constraint, as well as maybe the StudentName (or whatever you think you might need from Student). This way you can go back to your audit trail to piece together what has happened and when without worrying about whether you are hard or soft deleting parent records. This has the further advantage (or disadvantage, depending on your perspective) of keeping track of the changable parent table attributes as they were when the child record was originally recorded. For example, it might be useful to know that Student 123456, who is now Mrs. Marriedlady used to be Miss Singlegirl when her biology degree was conferred.

查看更多
爷的心禁止访问
4楼-- · 2019-01-22 07:11

Your live schema enforces relational integrity so you don't need foreign keys in the History schema. Or put it another way: the only reason to enforce foreign keys between tables in the History schema is if there is some mechanism for executing DML against the History schema other than populating it from changes in the live schema. In which case your History schema is pretty useless as an audit trail.

You raise the question of soft deletes, which confuses the issue. That would only be relevant if you're considering having foreign keys between the two schemas e.g. StudentScoreHistory references StudentScore. That can be a valid design, but again, it suggests you don't trust your audit mechanism. Personally I would prefer to have hard deletes in the live tables, and record the fact of deletion in the History table. Soft deletes are just another source of grief.

But anyway this is a different question. It is perfectly possible to have foreign keys between the live and history versions of each table e.g. StudentScoreHistory -> StudentScore without also enforcing the relational integrity within the History schema e.g, StudentScoreHistory -> StudentHistory.

查看更多
登录 后发表回答