Maintaining Referential Integrity - Good or Bad?

Asked 2011-04-26T17:54:11.417

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 exte

Edit Report

2 Answers

If you need to re-create the scenario, then I'd say yes you need the FKs, and having them I think would be an easier way to track through to the relevant related detail records. However, this makes deletions an issue, as well as info that may change in your primary key tables. In this case I'd say you don't want to delete records that have FKs in other tables, but rather use a soft delete as you've already indicated.

As far as info in the PK tables changing, caveat emptor. Setting up FKs would be a simple way to get some traceback ability, but it won't be perfect. There are trade-offs. To get an absolutely perfect history, you basically would need to create backup copies of all related records, any time an audit candidate record has something happen on it. You need to figure out the level of granularity that's appropriate and go with it, because a perfect record of events could be complicated to set up, and eat up a lot of space in the process.

Also, this may or may not be an option for you, but I would strongly consider a combination of tools like ApexSQL Audit + ApexSQL Log as opposed to a homegrown auditing solution. Based on your needs, those two tools combined with archiving your transaction logs periodically would cover what you need to do. The audit tool can store data in the same db or elsewhere, and the log tool can selectively recover data. Just a thought.

answered 2011-05-17T18:17:16.923

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.

answered 2011-05-17T14:55:12.367

Maintaining Referential Integrity - Good or Bad?

2 Answers

Your Answer