1

I'm trying to come up with a decent table design for a table that will log CRUD operations for a small application. There won't be a lot of traffic, which could justify a single table for all events. So far, my idea looks like this

ID int identity primary key, 
[Schema] nvarchar(128),
[Table] nvarchar(128),
ObjectID int, -- primary key of the object we're logging
Action nchar(1) , -- the type of event (I/U/D, short for INSERT, UPDATE etc
Field nvarchar(128), -- the changed column, in the event of UPDATE
OldValue nvarchar(1000),
NewValue nvarchar(1000),
ChangedAt datetime2,
ChangedBy nvarchar(128)

This would cover most of our use cases. There is, however, one itch with this design, and that is when I want to log changes to records where the Primary Key spans multiple columns, aka a Composite Primary Key.

In order to satisfy this need, I would have to extend the proposed table-design with yet another ID-column, allowing us to store both sides of the composite primary key.

You probably see that this is heading down the wrong path. What if I later need to log events for a table containing a Primary Key spanning 3 columns or more? Simply extending the log-table with "ObjectID_2" and "ObjectID_3" columns sounds asinine. Not to mention that the majority events will only have NULL-markers in those columns, as most our tables use single-column primary keys.

I am no fan of generic tables and column in general, and I'm starting to think a better solution would be to keep a separate log-table for each table that needs auditing. Am I going about this the wrong way?

Change Tracking does not solve this problem. The log-table needs to be exposed to the web and in a human-readable format. I have considered both CDC and Temporal Tables, but then we'd already be in the realm of "log everything for a single table", which is not my use-case. The log-table will be used by the application to log whatever events they care about, not every DML operation. That's is why I opted for a single-table solution.

0

Your issue sounds like it colud make use of a dimensional model, with the objects to be tracked as Dimension(s) and the events to be measured as Facts, especially if this table needs to be human readable and exposed to end users. Check out this resource for further reading BACK TO DIMENSIONAL MODELING BASICS and The 10 Essential Rules of Dimensional Modeling

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.