Sql-server – Generic log-table for CRUD operations that support both single and composite Primary Keys

database-designsql serversql-server-2016

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.

Best Answer

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

Related Solutions

Sql-server – Single Identity column and composite key, which to make primary

This seriously depends on the expected half-life of your project, on how agile your environment is and so on. If your project is there to last, and changes are possible, I would not assume that your composite PK is never going to change. I have seen too many projects burned by such assumptions.

One good example would be the use of Social Security Number. Long ago it was a common practice to use it as a natural PK. Later on, many systems had to restrict its use for privacy reasons, and it became not possible to propagate SS#s all over child tables.

The systems that used identities adjusted to the change easily. The systems that used SS#s in child tables had to go through a major overhaul.

So, in many cases it is cheaper to use identities as PKs, adding a UNIQUE constraint on what currently is considered to be unique, and referring child tables to ParentID. This way we have to do less work to adjust when the situation changes.

Sql-server – Do I need a separate Id column for this “mapping” table

If you have a one-to-many relationship between Producers and Products (in other words, a product can only belong to one producer), than it would make sense to just put a foreign key reference directly in your Products table:

One-To-Many

create table Producer ( id int identity(1, 1) not null primary key clustered, Name varchar(100) not null ) go create table Product ( id int identity(1, 1) not null, Name varchar(100) not null, ProducerId int not null foreign key references Producer(id) ) go

But if there's a chance that this will be a many-to-many relationship, then your best bet would be to use a Join table.

Many-To-Many

create table Producer
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

create table Product
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

create table ProductProducer
(
    ProductId int not null foreign key references Product(id),
    ProducerId int not null foreign key references Producer(id)
)
go

-- adding the primary key also ensures uniqueness
alter table ProductProducer
add constraint PK_ProductProducer 
primary key (ProductId, ProducerId)
go

If you decide to go with the Join table, you wouldn't need to have an additional key, as the combination of ProductId/ProducerId would ultimately be unique. You could use them as a composite key, so you wouldn't need that additional Id field in ProductProducer.

Best Answer

Related Solutions

Sql-server – Single Identity column and composite key, which to make primary

Sql-server – Do I need a separate Id column for this “mapping” table

Related Question