Denormalising

What is Denormalisation?

Denormalisation is the process of modifying a normalised database to improve performance by reducing the number of joins required for data retrieval.

It introduces controlled redundancy to enhance query efficiency, particularly in read-heavy applications.

Advantages and Disadvantages of Normalisation and Denormalisation

Aspect Normalisation Denormalisation
Performance Slower for complex queries due to multiple joins.
Effect: Higher latency on read-heavy analytics; writes remain efficient.
 Faster retrieval due to fewer joins.
Effect: Lower read latency, but more work to keep duplicated data in sync on writes.
Storage Efficiency Reduces data redundancy.
Effect: Smaller tables/indexes; better cache usage and lower storage cost.
 Increases data duplication.
Effect: Larger tables; more memory/disk usage and potential cache pressure.
Data Integrity Strong enforcement of integrity constraints.
Effect: Single source of truth; fewer update/insert/delete anomalies.
 More risk of inconsistency.
Effect: Copies must be kept consistent in Extract, Transform, and Load (ETL) logic; higher anomaly risk.
Query Complexity Requires complex joins to retrieve data.
Effect: Harder SQL and ORM mappings; may need extra indexes or views.
 Simplifies queries, making them more efficient.
Effect: Easier reads, but complexity shifts to maintenance of redundant fields.
Use Case Transactional systems (e.g. banking, ERP).
Effect: Best for Online Transaction Processing (OLTP): frequent writes/updates with strict consistency.
 Analytical systems (e.g. data warehouses, reporting).
Effect: Best for Online Analytical Processing (OLAP): fast reads/aggregations across large datasets.

When Is Denormalisation Useful?

Read-Intensive Applications

When performance is a higher priority than storage minimisation.

Normalised Query:

SELECT o.OrderID, c.CustomerName, p.ProductName, od.Quantity, p.Price
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
ORDER BY o.OrderDate DESC
LIMIT 50;

Denormalised: Store CustomerName and ProductName directly in Orders.

Simplified Query:

SELECT OrderID, CustomerName, ProductName, Quantity, Price
FROM Orders
ORDER BY OrderDate DESC
LIMIT 50;

Data Warehousing

Used in analytical systems where complex queries run frequently.

Normalised Query:

SELECT p.CategoryID, c.CategoryName, SUM(od.Quantity) AS TotalSold
FROM OrderDetails od
JOIN Products p ON od.ProductID = p.ProductID
JOIN Categories c ON p.CategoryID = c.CategoryID
GROUP BY p.CategoryID, c.CategoryName;

Denormalised: Store CategoryName in Products.

Simplified Query:

SELECT CategoryName, SUM(Quantity)
FROM OrderDetails
JOIN Products ON OrderDetails.ProductID = Products.ProductID
GROUP BY CategoryName;

High Traffic Systems

Websites and applications serving thousands of users simultaneously.

Normalised Query:

SELECT p.ProductID, p.Name, p.Price, c.CategoryName
FROM Products p
JOIN Categories c ON p.CategoryID = c.CategoryID;

Denormalised: Store CategoryName in Products.

Simplified Query:

SELECT ProductID, Name, Price, CategoryName
FROM Products;

Reducing Expensive Joins

Normalised Query:

SELECT o.OrderID, c.Name AS CustomerName, p.Name AS ProductName, od.Quantity
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID;

Denormalised: Store CustomerName and ProductName directly in Orders.

Simplified Query:

SELECT OrderID, CustomerName, ProductName, Quantity
FROM Orders;

Example: E-Commerce Platform

Normalised Database:

Customers(CustomerID, Name, Email, Address)
Orders(OrderID, CustomerID, OrderDate)
OrderDetails(OrderDetailID, OrderID, ProductID, Quantity)
Products(ProductID, Name, Price, Category)

Denormalised Database:

Orders(OrderID, CustomerID, CustomerName, CustomerEmail, OrderDate, ProductID, ProductName, Price, Quantity)

Advantage: Reduces joins and improves performance for frequently accessed reports.

Trade-offs of Denormalisation

  • Faster Queries: Reduces need for joins, improving performance.
  • Increased Storage: Duplicated data uses more space.
  • Risk of Inconsistency: Repeated values can become inconsistent if not updated properly.
  • Better Scalability: Optimised for fast reads, especially in distributed environments.

 Key Takeaways

  • Denormalisation improves query speed by reducing joins through intentional redundancy.
  • Useful in read-heavy applications such as dashboards and reports.
  • Best suited to analytical or high-traffic systems where performance outweighs strict data integrity.
  • Normalisation is still preferred in transactional systems where consistency matters most.