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_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.