DB Design for record tracking

Question

Background: I am writing an app and designing out the database using efcore. There are particular tables that store user actions that I need to keep track of who created the record, last modified it, and deleted it (soft deletes).

I have a user table that has an int as a PK and each of the respective field (CreatedBy, LastModifiedBy, DeletedBy) hold an int that points to a user row.

I do have a full audit setup where the entirety of a row has it's old/new contents stored on save and that works fine. But this particular question is about the more immediate created/modified/deleted by tracking.

Help desk is generally who uses these fields on a daily basis to help users determine whats going on quickly but there are a lot of places in the app itself that will draw upon those fields eventually (moreso created/modified from the app perspective).

Question: I was going to create a pk/fk relationship between the tables and the user table. However, it got me thinking about if there is a better strategy then adding those 3 fields and relationships to every single table going forward. Maybe a single table that stores the table name with its pk and created/modified/deleted columns that have a relationship back to the user table so that only 1 table has those pk/fk relationships back to user. I just feel that there must be a better way/more efficient way to handle this. Is there a better way to handle this?

Answer 1

Don't do what you are thinking, stick with your original design - keep all auditing fields as they related to each table on the table itself. Adding a table that stores auditing fields from other tables just creates a design nightmare.

Now, a bigger question is how to track the audit transaction. A design I like is as follows:

• CreateBy. Add default binding SUBSTRING(SUSER_NAME(),1,50)
• CreateTs. Add default binding GETDATE()
• UpdateBy
• UpdateTs

Hard deletes are allowed (i.e., bad data). Soft deletes come in the form of an additional column called ActiveInd (BIT) where that transaction would be stored as an Update. This means that Updates and soft Deletes are recorded into the UpdateBy/UpdateTs columns.

That should get you what you need if you intend on tracking activity from a web application. If you have a back-end system that is loading and manipulating data that I would include a LoadInfo table that tracks all of the jobs and then you can add both a LoadSequenceKey and ParentSequenceKey (add a self referencing foreign key here) and then you can create a foreign key on all tables that are modified by jobs that store the sequence key as either a CreateSequenceKey or UpdateSequenceKey.