Database modelling
When denormalization makes sense
In the previous chapter, you saw why it is useful to give each fact a clear place to live.
For example:
1234567891011customers→ customer informationproducts→ product informationorders→ order informationorder_items→ information about products within ordersThis reduces unnecessary duplication and makes it easier to keep data consistent.
But there are situations where an application deliberately stores additional information that could otherwise be retrieved or calculated.
This is often called denormalization.
What is denormalization?
Denormalization means deliberately storing duplicated, derived, or precomputed data because doing so is useful for a particular workload.
Imagine we frequently need an order together with the customer's name.
With normalized tables, we can retrieve it with a join:
1234567SELECT o.id, o.total_amount, c.full_nameFROM orders oINNER JOIN customers c ON o.customer_id = c.id;A denormalized design might also store a copy of the customer name directly on the order:
123456ordersidcustomer_idcustomer_nametotal_amountThis can make some reads simpler.
But now the name may exist in both:
12customers.full_nameorders.customer_nameIf both columns are supposed to represent the customer's current name, the application has two copies that need to remain consistent.
That is the trade-off introduced by denormalization.
Preserving historical values
Sometimes two similar values are intentionally allowed to become different because they represent different facts.
Our database gives us a useful example.
The products table contains:
1pricewhile order_items contains:
1unit_priceImagine a product currently costs 20.00.
A customer buys it at that price.
Later, the product price changes to 25.00.
The product's current price should now be:
1products.price = 25.00But the historical order should still show:
1order_items.unit_price = 20.00These two values no longer mean the same thing:
12345products.price→ current product priceorder_items.unit_price→ price associated with this particular order itemKeeping the historical value is not an accidental inconsistency. It is intentional because the two columns represent different facts.
The same pattern is common with shipping addresses.
An application may keep:
1customers.current_addressand also store:
1orders.shipping_addressThe first represents where the customer lives now.
The second represents where a particular order was sent.
Changing one should not rewrite history in the other.
Storing precomputed values
Another reason to store additional data is to avoid repeatedly calculating the same result.
Imagine an order total has to be calculated from many order items:
1234567891011order_items │ ├── quantity × unit_price ├── quantity × unit_price └── quantity × unit_price │ ▼ SUM(...) │ ▼ order totalAn application could calculate this every time the order is requested.
Alternatively, it could calculate the value when the order changes and store the result in a column such as:
1orders.total_amountReading the order then becomes simpler.
The trade-off is that the stored total must remain consistent with the data it summarizes.
If an order item changes, the stored total may also need to change.
Precomputed summaries
The same idea can be applied to larger summaries.
Imagine a page that repeatedly needs:
123number of orders for a customertotal amount spentdate of latest orderThose values can be calculated from orders whenever the page loads.
But if the calculation is expensive and the page is read very frequently, an application might maintain something like:
123456customer_summarycustomer_idorder_counttotal_spentlast_order_atThe application can then read the summary directly.
The cost is that whenever the underlying orders change, the summary may also need to be updated.
Reporting and analytical workloads
Transactional application queries often work well with normalized tables.
Reporting workloads can have different requirements.
Imagine repeatedly analysing:
1234567customers ↓orders ↓order_items ↓productsto answer questions about customers, product categories, order values, and dates.
A reporting system might maintain a wider dataset such as:
123456order_idcustomer_countryproduct_categoryquantityunit_priceplaced_atSome information is now duplicated, but analytical queries may become simpler because the data has already been combined into a form designed for those reads.
The trade-off
A more normalized design generally gives each fact fewer places to live:
12345one authoritative copy ↓easier consistency ↓some queries require joins or calculationsA more denormalized design may store additional copies or summaries:
12345duplicated or precomputed data ↓some reads become simpler ↓more data must be maintainedDenormalization therefore does not simply make a database faster.
It changes where the work happens.
You may save work during reads while creating more work during writes and updates.
Do not denormalize just to avoid joins
A query containing a join is not automatically inefficient.
For example:
123456SELECT o.id, c.full_nameFROM orders oINNER JOIN customers c ON o.customer_id = c.id;is a normal way to retrieve related relational data.
PostgreSQL is designed to execute joins, and appropriate indexes and query plans can make them efficient.
So the existence of a JOIN is not by itself a reason to duplicate data.
Denormalization should solve a specific problem rather than being used preemptively.
Normalize first, denormalize deliberately
A useful default is to begin with a design where each fact has a clear owner.
Then, if a real requirement appears, you can deliberately store additional data.
Common reasons include:
preserving historical values
avoiding repeated expensive calculations
serving frequently requested summaries
supporting reporting or analytical workloads
When you denormalize, you should be able to answer three questions:
What information are we storing more than once or precomputing?
Why is storing it useful?
How will it stay correct when the underlying data changes?
Denormalization is therefore a design trade-off, not the opposite of good database design.