Denormalizing When There Is No Source of Truth

On a performance management platform I worked on, we denormalized part of the data model for a reason that had little to do with read speed. The organizational tables that were supposed to be our single source of truth had stopped being reliable, and we needed a design that didn't depend on them. That meant breaking normalization rules on purpose.

The Problem

The organizational management data was meant to be the foundation everything else relied on. It was a chain of normalized tables:

• Employee: the master record for each person
• Position and position variant: the role and its variations
• Assignment: links an employee to a position variant
• Position-organization mapping: places positions in the org structure
• Group master: the organizational units, each belonging to exactly one company

This was the intended single source of truth, but the data was wrong in almost every layer. Some employees were missing. Some positions were mapped to the wrong organization, some groups to the wrong company, and some positions moved between organizations partway through a period. Part of the cause was process: the admins responsible for keeping the data healthy and the users who relied on it weren't in sync, and the gaps ended up in the database.

The Constraint

The performance management module had to score each employee's KPIs correctly, and a performance record belongs to a specific assignment at a specific time. If someone held position A in Q1 and Q2 and a different position from Q3, each period has its own performance data.

The module also relies on the hierarchy to control who can approve KPI planning for whom, and who can monitor KPI realization and achievement. A manager who saw wrong or missing subordinates could not, or would not, approve the planned KPIs. With a bad hierarchy, the system would be useless to live users.

So correctness depends on knowing who held which position, under which superior, in which period. With a live hierarchy that was shifting and full of errors, historical scores could go wrong without anyone noticing. The module also had to go live as quickly as possible, so waiting for the upstream data to be cleaned up wasn't an option.

The Decision

We stopped joining against the live hierarchy and built a single denormalized table that the module owns. Each row holds the employee number (the employee's primary ID), the position, the superior, and the period the relationship was valid. The superior is stored directly instead of derived each time.

With correct data, finding someone's superior or subordinates meant walking about five tables: the current position, then its organization, then the other positions in that organization, then the employees in those positions. That was tolerable while the data was right.

Before denormalization:

It stopped being tolerable when the data was wrong. If someone who should have five subordinates showed only four, I had to check the missing person's position, the link between that position and its organization, whether they were in the same organization as the manager, and so on, table by table. The new table reduces that to reading one row.

After denormalization:

The table is also a point-in-time snapshot, so later changes to the organization can't rewrite the history behind a score. And it decouples the module from the main platform's hierarchy, which we no longer trusted.

Trade-offs and Justification

Denormalization is usually justified by read performance. Here the justification was correctness and diagnosability: when the data is wrong, one table to inspect and fix beats five.

• Gain: a data fix or investigation that used to span five or more tables now touches one.
• Gain: the module could go live on data it controlled, without waiting for upstream cleanup.
• Cost: the snapshot duplicates data that also lives upstream, so it can drift from the live tables if nobody maintains it.
• Cost: the team has to know that this table, not the hierarchy, is authoritative for performance.

I accepted those costs because the alternative was building on data we knew was wrong.

Which Normal Forms We Broke

The table breaks 3NF, and BCNF with it. Normal forms are about dependencies between columns, so it helps to look at which columns depend on which.

• 1NF and 2NF hold. Each row is a single atomic fact, and every column depends on the whole key: employee number plus the validity period.
• 3NF is violated. The superior isn't an independent fact about the employee. Upstream it is derived from the position and the organization that position belongs to. Storing it directly creates a transitive dependency: employee and period determine position, and position determines superior.
• BCNF is violated for the same reason: a non-key column determines another column.
• 4NF and 5NF aren't a concern. Each row records one relationship for one period, with no independent multi-valued facts mixed in.

The duplication is deliberate. Normalization assumes one current truth per fact, but here the fact is that this person held this position under this superior during this period. That is a historical record, similar to a Type 2 slowly changing dimension in data warehousing.

Since the schema no longer guards consistency, integrity moves into process: a non-overlapping period rule per employee, a foreign key on employee number, and a reconciliation check against the upstream tables.

Outcome

The module could ship on data it controlled, and corrections became a single-table change instead of a multi-table one.

Normalization assumes you can trust the upstream data. We couldn't, and the deadline was real, so I chose the unconventional design on purpose and accepted what it costs.