Best way to store immutable/read-only copies of data for logging?

Question

I'm storing data about tasks like so

TASK (id, name, employee_id, machine_id, ...)

For logging purposes I would like to store any information about that TASK once it was made. Since the reference fields might change or be removed (let's say we remove an employee or a machine, or change its hard_drive...), I want to make sure I log everything as it was when the task was done.

My first approach was to just have a field for everything like so

TASK_DONE (id, name, employee_name, ..., machine_hard_drive, machine_ram, ...)

but it made a lot of redundant data and a quite huge table (there are already about 5 referenced tables in TASK each with a lot of logging relevant fields)

I felt like having some other logging tables like MACHINE_ON_TASK and referencing only to their ids could spare a lot of redundancy, but it also means creating new tables for everything log-worthy. On the other hand, it might make more sense that a TASK and TASK_DONE (and their associated data) look the same.

What do you think would be a better way to approach or solve this problem?

I'm using SQL-server but I don't believe this is relevant here.

Answer 1

You could treat your reference tables as slowly changing dimensions and perform type 2 maintenance i.e. add a new reference row when any reference data changes. Over time you may choose to horizontally partition your reference tables into "active" and "archive" parts, depending on the data churn you experience. This can be achieved using using SQL Server's built-in partitioning functionality or a roll-your-own approach with two separate tables. Your needs at the time will dictate which.

There is no good reason why your active OLTP table and your logging table should look the same. They perform different roles and have different read and write requirements. If the logging table needs to be wide and sparse then that's what it needs to be. Create the objects to solve the problem you have.

And one last suggestion, which is defininetly from the "clutching at straws" bucket: define TASK_DONE (id int, reference_values xml). Extract your pertinent values at the point in time and save them away. This allows for changes in the reference data schema without having to bring existing log records up to the new schema. That will make historical searching more complicated, of course.

Answer 2

Another option would be to use "soft" deletes when you want to delete a machine or employee from those tables. Use a "deleted" column in those tables to mark which rows are deleted. You can create views that ignores deleted records, and use that to back most of your queries against employees or machines, but your queries on the task table will still account for soft-deleted records.

If you're also that concerned about changes to reference data (such as changing an employee's name), you could find a way to version those records, so an update to such tables would actually insert a new record, and your task table would still reference the old one. Of course doing this means that anything referencing the old record might need to be updated to point to the new one.