2

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?

  • all tables should have pks so yes. why do you have two ids on trackeddbentity? why would an audit table have a col called *modified? – Ewan Apr 15 '18 at 13:48
  • In the original post I described what is the PK of the audit table, as well as the reason for two Ids. As for your last question, the row inserted into an audit table is the exact copy of the original, unmodified row, and since the main table has information on when it was last modified, the same information is then copied into audit row. Let's stay on the topic though. – Amai Apr 15 '18 at 14:49
  • "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)." - does it mean that when you change the ID you also edit all the audit records? – Maciej Stachowski Aug 15 '18 at 0:41
-1

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.

  • I'm not quite sure how to approach this answer. I get the impression that it is focusing on 'is my architecture good' rather than the question asked in the topic. I've already discussed the db architecture with a db architect, there's no need to ponder it further (and it is not what this post is about). Concurrent editing and other problems you mention - that's handled already by the server. The basic version of this new audit-feature is working well and problem-free atm. I'm just wondering if changing from composite pk (id+modDate) to standalone guid pk will make my life easier in the future. – Amai Apr 15 '18 at 17:07
  • Your method works. the flaw is that if two event happen within your time resolution (or you change the int id) its possible to get a duplicate and your audit insert will fail. But this can be mitigated for the unusual style of logging you are doing. But. yeah, to a future reader wondering 'should audits have thier own pk?'. yes they normally need one. – Ewan Apr 15 '18 at 18:45
  • going off your sample code, without the extra info you give in the comments. then the int id you can 'change if needed' and the presumably .net? datetime mean you will get duplicate audit keys and errors quite a lot. Thats why I asked – Ewan Apr 15 '18 at 18:54
  • 1
    The FKs referring to Id of the main table are created with ON UPDATE CASCADE and the Id itself is unique to make sure there are no duplicates at any time. Why then would I get duplicate audit keys and errors? I tried to provide only the information essential to the question, didn't see any reason to delve into all the implementation details as they make the discussion stray away from its point. Please update your answer to make it clear as to what is your stand on the matter so that future viewers won't have to delve into the comments to find it. – Amai Apr 15 '18 at 19:48
  • hmm im not convinced that will work. but, like you say the details arent important. You can make it work im sure. It seems a lot of trouble to goto to spare yourself an extra id col, but im sure you have good reasons for wanting it that way. – Ewan Apr 15 '18 at 19:59
-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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.