Denormalization and when to use it in SQL

Introduction

Denormalization is used to make data easier and faster to read by adding some repeated information. It helps when you want quick answers without joining many tables.

When your database queries are slow because they need to join many tables.

When you want to show reports quickly without waiting for complex calculations.

When your data changes rarely but is read very often.

When you want to reduce the number of database joins to improve performance.

When you have a simple app that needs fast data access and can handle some repeated data.

Syntax

SQL

There is no single SQL command for denormalization.
It means you add extra columns or tables that repeat some data to avoid joins.
Example: Adding a 'customer_name' column in an 'orders' table even if it exists in 'customers' table.

Denormalization is a design choice, not a specific SQL command.

It trades some extra storage and possible data updates for faster reads.

Examples

This is a normalized design where customer name is stored only once.

SQL

-- Normalized tables
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(100)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE
);

Here, customer_name is repeated in orders to avoid joining with customers table.

SQL

-- Denormalized table
CREATE TABLE orders_denormalized (
  order_id INT PRIMARY KEY,
  customer_id INT,
  customer_name VARCHAR(100),
  order_date DATE
);

Sample Program

This example shows how normalized tables require a join to get customer names, while denormalized table stores customer names directly for faster queries.

SQL

-- Create normalized tables
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(100)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE
);

-- Insert sample data
INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO orders VALUES (101, 1, '2024-01-01'), (102, 2, '2024-01-02');

-- Query with join (normalized)
SELECT o.order_id, c.customer_name, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- Create denormalized table
CREATE TABLE orders_denormalized (
  order_id INT PRIMARY KEY,
  customer_id INT,
  customer_name VARCHAR(100),
  order_date DATE
);

-- Insert denormalized data
INSERT INTO orders_denormalized VALUES
(101, 1, 'Alice', '2024-01-01'),
(102, 2, 'Bob', '2024-01-02');

-- Query denormalized table
SELECT order_id, customer_name, order_date FROM orders_denormalized;

Important Notes

Denormalization can cause data duplication, so updates must be done carefully to keep data consistent.

It is best used when read speed is more important than write speed or storage space.

Always weigh the benefits of faster reads against the cost of maintaining duplicated data.

Summary

Denormalization adds repeated data to make reading faster.

Use it when you want quick access and can handle some data duplication.

It reduces the need for joins but requires careful updates.