Natural Key Audit Table

Question

I'm designing SQL Server tables with Natural Keys instead of a Surrogate Keys for the Primary Key. The problem I've run into is that it doesn't work well with the Audit table format that I've used with Surrogate Key tables in the past. Typically I'll create an audit table that has the same columns as the table being audited. A trigger on the table being audited writes a new row to the audit table that matches the state of the row before update or delete. To enforce integrity I use both the Surrogate Key and modified date columns as a composite PK for the table. If I don't use a Surrogate Key then I cannot track changes if one of the columns that make up the composite key changes.

Log table example with Surrogate Key:

LogId (PK)
LogType
Data
ModifedDate
ModifedBy

LogAudit Table for Log table with Surrogate Key:

LogId (PK)
LogType
Data
ModifedDate (PK)
ModifedBy 


Log table example with Natural Key:

LogType (PK)
Data
ModifedDate (PK)
ModifedBy

LogAudit Table for Log table with Natural Key:
LogType 
Data
ModifedDate
ModifedBy

How do you track changes for Natural Key Log table record in the Audit table if LogType or ModifiedDate change?

I don't really understand what you're asking. Your basic design seems fine (the PK of the audit table is the PK of the source table plus a timestamp of some kind), so it isn't obvious what your problem actually is. It might also be helpful if you edit your question to show your table structures as real DDL instead of text, to remove any ambiguity about what the keys really are. And if your problem is populating the audit table, then how are you trying to do it and what difficulties are you having? — Pondlife
If I use the Natural Key version and either the LogType or ModifiedDate change, then I lost the link to reference the older records I've audited. Unlike the surrogate key version, I can query on logID and find the history of changes in the Audit table. — Josh

Damir Sudarevic Damir Sudarevic · Accepted Answer · 2013-01-08T19:00:28

I think you misunderstood the basic concept here.

If it is true that {LogType, ModifedDate} is a natural key in the Log table (case 2), then it is also true that it is an alternate key (AK) (unique) in the first example.

So the Log table in the first example would look like this.

LogId        (PK)
LogType      (AK1.1)
Data
ModifedDate  (AK1.2)
ModifedBy

So, for a given {LogID} the {LogType, ModifedDate} can not change, and vice versa.

But, this is not what you are trying to accomplish. The answer is probably that your Log table does not have "natural key" in the first place.

EDIT as per comments

As a rule-of-thumb, for creating a row-based audit table:

Copy the original table structure (columns).
Add ChangedAt (datetime) and ChangedBy columns to the table.
Add ChangedAt column to the original PK.
Add copy of PK columns to the table. This is to capture any PK changes.
Add FKs with ON UPDATE CASCADE, ON DELETE NO ACTION. This prevents hard-deletes, so use soft deletes.
Populate from an AFTER INSERT, UPDATE trigger on the original table.

Example 1

      Table: {ID, SomeData}                               ; key: {ID} 
audit table: {ID, SomeData, ChangedAt, ChangedBy, ID_CPY} ; key: {ID, ChangedAt}

Example 2

      Table: {UserID, UserOrderNo, SomeData}                       ; key: {UserID, UserOrderNo} 
audit table: {UserID, UserOrderNo, SomeData, ChangedAt, ChangedBy, UserID_CPY, UserOrderNo_CPY} ; key: {UserID, UserOrderNo, ChangedAt}

Example 3 (as per your description)

      Table: {LogType, ModifedDate, SomeData}                       ; key: {LogType, ModifedDate} 
audit table: {LogType, ModifedDate, SomeData, ChangedAt, ChangedBy, LogType_CPY, ModifedDate_CPY} ; key: {LogType, ModifedDate, ChangedAt}

Natural Key Audit Table

2 Answers