Best way to store immutable/read-only copies of data for logging? 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.
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.