Logging, with optional foreign key cross reference

Question

I am wanting to enhance a system to execute various background tasks (primarily data importation). In order to provide data for support, analysis and job status generally, I had been thinking of implementing a related table to record task updates e.g.

datetime message                
10:00    Job started: status change to EXECUTING
10:01    Job failed because record 101 of file abc.csv is badly formed
10:02    Job status changed to ERROR

...etc.

At some point I thought that this is similar to a regular process log, and so I should customise the SQL sink/appender/provider of a regular log library (e.g. log4net) rather than reinvent the wheel, and then I can also use this for other various logging needs. I had been planning of extending the log model to have a polymorphic relationship with my ImportTask table, and whatever else that might come along after.

e.g.

datetime model model_id message                
10:00    Job   22       Job started: status change to EXECUTING
10:01    Job   22       Job failed because record 101 of file abc.csv is badly formed
10:02    Job   22       Job status changed to ERROR
10:03    <null><null>   Some other thing happened

...etc.

So then I went looking for examples of this kind of solution and found... nothing. Not even a simple addition of foreign key to a logger. This makes me think I might be on the wrong track.

I did find examples where people are embedding data into the log entry (e.g. as json), however this will not do, as I wish to present the user with the data of the ImportJob, along with related log entries for that item (if the cross reference were encoded json in the log table, then I would have load and decode every log entry to find the related items).

Does adding foreign key attributes to the logging appender make sense, or should I build a custom ImportJobLog type model/table, and leave log4net for general purpose logging?

Answer 1

Structured logging is a good idea, since you can query more easily for relevant events. For example, you could build a view that lists all log entries that relate to a particular object or transaction.

However:

• Log events typically have wildly different shapes/fields/properties, depending on the type of the event. This does not fit neatly into a relational model, except perhaps for a handful of shared fields like "timestamp" or "severity". That's why JSON data and NoSQL storage is so common for structured logging.

• The most important feature of a logging target is that it actually stores all logs. Incoming events should not be rejected just because they have an additional property or because they are violating a foreign key constraint. It is better to have "inconsistent" log data if that data is the truth, than to reject actual log events.

• If you start creating foreign key references between log entries and your application's data model, this is an irreversible decision to tie those two datasets together. This has conceptual problems, such as the inability to delete records without also deleting the related logs. This could also lead to performance problems, as normal OLTP style database operations have very different access patterns from log files (write-heavy, append-only, rarely read).

So in your scenario, it is potentially a good idea to configure your logging system to have a nullable model_id column. I'm very sceptical though about imposing any constraints on the log events table, such as foreign key references. If you have structured data, you can write queries to join your logs with other database entities, regardless of whether they share a database schema or whether you are imposing foreign key constraints.