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:

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

Environment: