Should audit records have their own id?

Question

I'm creating audit tables to track changes made to some of my crucial tables and I'm wondering whether there's a point to history rows having their own id. Here's how my interfaces look like:

public interface ITrackedDbEntity : IDbEntity
{
    Guid GUID { get; } //pk, unique
    int Id { get; set; } //unique, not pk

    string CreatedBy { get; set; }
    DateTime CreatedAt { get; set; }
    string LastModifiedBy { get; set; }
    DateTime LastModifiedAt { get; set; }
}
public interface IHistoryDbEntity : ITrackedDbEntity
{
    string ActionType { get; set; }
}

GUID is the actual unique, immutable identifier here and it is used internally in my system, while Id is a sort of additional identifier which I can change if needed and safely share to the outside (I also have a foreign key relation from audit table to it). Now, as for the audit table primary key, I thought a composite key of Id and LastModifiedAt would suffice. That would even allow me to create a foreign key relation between main table guid and audit table guid for easier querying.
But what I'm wondering about and what my question is: is there any valid reason for creating an unique AuditId for the audit tables?

Answer 1

I would not add a guid/unique ID to an audit table of another table. If your audit table is auditing some other process that may involve data from several tables, that is a different story.

If there was a reason to make it easy to identify a specific record for data modification, I would avoid compound keys. Compound keys make joins more complicated, but there is rarely a need to do that with an audit table.

Audit tables are an acceptable reason to have a compound key. Normally, I'm against them.

Answer 2

I would expect an audit table to look like this:

Audit
   id - guid
   actionTakenOnObjectId - guid, possibly fk to object table
   typeOfObjectActionTakenOn - possibly have this if you are sharing the audit table with multiple other tables.
   actionType
   actionDate - UTCDateTime
   actionData - string data associated with the change. ie first name from x to y
   userId - guid, Id of the user that took the action

The key thing with the audit table is that you are always able to add entries. With this design the GUID PK means you will never get a duplicate record and can insert records from multiple computers and threads as you like.

Also, you can record things like : user x viewed record y, where no data change takes place.

In your situation you have a couple of odd things.

1. the extra int Id col. I'm not sure what your reasons are for this and you say it can change. Lets leave it out of the equation for now
2. You are making an exact duplicate of the existing row rather than just storing a subset of info about the event.

Normally I would say that a datetime column is doesn't have enough resolution to be a primary key. You can have two events happening at the same time and the second will fail to insert due to having a duplicate key.

However, in your case because your audit is directly related to a change of the database row, it would be possible to encapsulate the update and audit insert in the same transaction. Adding a tick onto the LastUpdatedDate where you detect that it hasn't changed since the last update.

Assuming you use the unchanging GUID rather than the changeable int id it would then be possible to ensure that the time is always unique and you can always insert an audit record.

If you do take this approach though, you might be better served by instead adding a LastVersion - guid column which explicitly references the previous audit record for the row.

Overall though this is complex approach, akin to Event Sourcing. and relies on enforcing the order of object change events. If you get two changes to the same version of the object. say I update the name, while another user updates the address. Then you may want to reject the second change with a "Another user has changed the object while you were editing" style message.

Obviously every case is different, but, I think in general I would advise for using a new PK on an audit table and against having an audit table which I suppose is a kind of transaction log for another table.