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.