01 | WHY WOULD WE REINTRODUCE DUPLICATION?

An online shop displays product information thousands of times, but its category names change only occasionally. A normalized design keeps each category name in one place. Every product listing may join PRODUCT and CATEGORY to display that name.

Suppose measurements show these reads are too slow, even after appropriate query and index tuning. Could storing a copy of the category name beside each product help?

Denormalization deliberately introduces redundancy or stores combined or precomputed data to support particular queries. It trades some simplicity of maintenance for potentially faster reads. It is a deliberate design decision—not a reason to ignore dependencies.

Start from an understood, normalized model. Change selected parts only when the expected benefit is worth the cost.

02 | THE NORMALIZED DESIGN

To show a product with its category name, join the two tables through CategoryID. This is normal relational database work. With suitable indexes and a good execution plan, the join may already be fast enough.

Rename C10 once in CATEGORY and every query using current source data sees the new name. This is a major advantage when consistency matters.

03 | A DENORMALIZED READ TABLE

A listing query can now obtain the displayed fields from one structure. It may avoid repeatedly joining the category data, but the category name is duplicated.

Notice the dependency: ProductID → CategoryID → CategoryName. If PRODUCT_LISTING is treated as a normal base table under these rules, the category name creates a non-key transitive dependency. That is the deliberate trade-off.

A safer option may be to keep PRODUCT and CATEGORY as the authoritative source and generate PRODUCT_LISTING as a read model. If the source changes, the read model must be refreshed.

04 | WHAT HAPPENS WHEN A NAME CHANGES?

In a directly maintained denormalized table, renaming a category may require updates to every associated row. A missed row creates an update anomaly. More affected rows can also mean more work during writes.

In a periodically refreshed read model, old values may be intentional until the next refresh. This is staleness: the read copy lags behind the source. It still needs a clear freshness policy and users must not assume it is current.

Prevent accidental inconsistency by defining one source of truth, coordinating dependent updates, checking copies, and providing a rebuild or refresh process. The exact mechanism depends on the database and the application.

05 | COMPARE THE TRADE-OFFS

“Denormalized means faster” is too broad. Wider rows, extra storage, synchronization overhead and different query patterns can offset the benefit. Likewise, normalization does not automatically make every write or query fast.

06 | READ-INTENSIVE APPLICATIONS

A read-intensive workload has frequent retrievals compared with changes. Examples include product catalogues, public reporting dashboards and some analytics systems. Repeating an expensive join or aggregation for many users can justify a precomputed result.

It is not just the read/write ratio that matters. Ask whether the slow operation is actually a join or repeated calculation, how frequently the copied facts change, and how current the result must be.

A write-intensive transaction system with frequently changing facts may gain little from duplicated fields. A separate reporting model can let an organization keep normalized transactions while supporting faster analytical reads.

07 | PRECOMPUTE A SUMMARY

A report can read UnitsSold rather than add every order-line quantity again. However, a returned item, cancelled order or corrected quantity can change the correct total. The summary needs the same business definition as the report and must be maintained accordingly.

A materialized view stores a query result. Unlike a normal non-materialized view, it can avoid recomputing that result on each read. Depending on the system, it may be maintained automatically or refreshed separately. Maintenance still costs work.

A normal view can simplify SQL text without storing a redundant copy. Therefore, simpler query text alone is not proof of denormalization or improved performance.

08 | CONSIDER ALTERNATIVES FIRST

• Indexes: suitable indexes can reduce searching and improve joins, with their own write and storage costs.
• Query tuning: retrieve only needed rows and fields; inspect how the database executes the query.
• Ordinary views: make repeated query definitions easier to use without necessarily storing their results.
• Caching: reuse a result where its lifetime and invalidation rules are appropriate.
• Selective read models: keep the authoritative schema normalized and denormalize only a targeted reporting structure.

Measure realistic workloads before and after a change. Compare read latency, write latency, storage, refresh cost and freshness. A result on one small test dataset is not enough to guarantee production performance.

09 | EVALUATE WITH EVIDENCE

“Evaluate” means weighing both sides and reaching a justified conclusion for the scenario. Use benefit → cost → conditions → recommendation.

Sample evaluation: the summary improves the measured report response substantially, benefiting a frequently read dashboard. It adds storage and maintenance and can show older values. If the report tolerates approximately one-hour-old data and refresh costs are acceptable, keep the normalized source and use the summary for this report. If the same data supports a decision requiring current stock, use an appropriately current source for that decision.

A refresh failure can make data older than the normal refresh interval. Monitoring and a visible “last updated” time help users assess this risk.

10 | KEEP DIFFERENT FACTS DISTINCT

Do not mistake historical facts for redundant copies. In A3.2.6, UnitPriceAtOrder is the price actually charged, while CurrentPrice is today’s product price. They describe different facts and should not be kept equal.

Likewise, repeatedly storing a foreign key to represent relationships is normal. Denormalization concerns deliberately repeated or derived facts, not merely any repeated value.

Decision checklist: what query is slow, what evidence shows the cause, what will be copied, where is the source of truth, how often does it change, how fresh must it be, and how will it be repaired if updates fail?

Further reading: Microsoft’s materialized view pattern.