Search code examples
.netdatabasedatabase-designauditaudit-trail

issue designing audit table to track changes

I am new to database designing and I am designing a system which creates an identical audit record on every insert, update and delete.

Below is the design of MyTable and MyAuditTable

enter image description here

RefId1, RefId2 (both integers) in "MyTable" are reference keys to other tables, with enforced referential integrity.

Do I need to have RefId1, RefId2 in "MyAuditTable" enforce the referential integrity or have them as just integer values and not enforce the integrity?

What should be my ideal design?

Cheers!

Solution

  • I would say no.

    As you may not want to constrain an historical value.

    Why do you need and Id on and audit table?

    I go one step further and put Ref1Value and Ref2Value (not ID) so I can change it and record the value in history that it was at that time.