Last time, I built a methodology and then immediately used it on only one of three examples. If you noticed I left the audit trail and soft delete examples hanging with a diagnosis and no treatment. Partly because the post was already getting long, and partly because the audit trail problem led me somewhere I wasn’t ready to write about yet: temporal modeling.

I’ve since gone down that rabbit hole. It’s deeper than I expected, and honestly a bit humbling. Turns out “preserve what things looked like at a point in time” is a problem the database community has been formally thinking about for decades and people keep reinventing broken versions of the solution.

So this post finishes what the last one started. Both examples, all six steps, no punting.

Revisiting Example 2: The denormalized audit trail

The culprit:

CREATE TABLE user_audit (
    audit_id INT PRIMARY KEY,
    user_id INT,
    email VARCHAR(100),
    department_name VARCHAR(100),  -- Copied from departments table
    manager_name VARCHAR(100),     -- Copied from users table
    changed_at TIMESTAMP,
    changed_by INT
);

Initial approach was we keep exactly what things looked like in the moment of chage thus showing the accurate values at that particular point, like maintaining a history.

Revisiting the problems

Problem 1: The audit trail becomes misleading

At some point in time the department_name changed and thus changing the results from the query and also creates confusion while going through the audit trail.

The solution: