SQL Denormalization — Balancing Performance with Redundancy

A frequent school report performs an expensive summary over many results. A stored summary may help, but new marks can make it stale.

Denormalization deliberately duplicates or precomputes selected information to serve a measured read requirement, with an explicit policy for keeping that information correct.

Start with the accurate normalized data and identify the expensive report through measurement. A duplicate field or summary table creates another representation that writes or refresh jobs must maintain. Document which representation is authoritative and how readers learn when the summary is current.

Related concepts: Views and Tune SQL Performance and Transactions.

Denormalization is the process of combining data from multiple related tables into one to reduce joins and improve query speed. While it introduces redundancy, the trade-off can be worth it — especially for read-heavy systems.

• To improve query performance
• To reduce the number of JOINs
• To simplify reporting or analytics queries
• When data is mostly read and rarely updated

SQL · portable concepts; preserved examples identify their engine

Normalized Design

-- Table: students
CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10)
);

-- Table: results
CREATE TABLE results (
  roll_no INT,
  subject VARCHAR(30),
  marks INT,
  FOREIGN KEY (roll_no) REFERENCES students(roll_no)
);

-- Table: subjects
CREATE TABLE subjects (
  subject VARCHAR(30) PRIMARY KEY,
  teacher VARCHAR(50)
);

To get full student result with teacher:

SELECT s.name, s.class, r.subject, r.marks, sub.teacher
FROM students s
JOIN results r ON s.roll_no = r.roll_no
JOIN subjects sub ON r.subject = sub.subject;

This query is correct and efficient for small data. But if millions of records grow and performance lags, consider denormalization.

SQL · portable concepts; preserved examples identify their engine

CREATE TABLE student_report (
  roll_no INT,
  name VARCHAR(50),
  class VARCHAR(10),
  subject VARCHAR(30),
  marks INT,
  teacher VARCHAR(50)
);

Now to fetch student result with teacher:

SELECT name, class, subject, marks, teacher
FROM student_report
WHERE roll_no = 1;

name           | class | subject | marks | teacher
----------------+--------+---------+--------+-----------
Aarav Sharma   | 10A   | Maths   | 85    | Mr. Nair
Aarav Sharma   | 10A   | Science | 88    | Ms. Banerjee

• Data redundancy – same teacher name stored multiple times
• Update anomalies – if a teacher is reassigned, you must update many rows
• More storage – although storage is cheap, it's still a factor

• You have read-heavy workloads (e.g., dashboards, analytics)
• Joins are causing slow queries
• Updates are infrequent and acceptable to handle manually or via scripts
• You want to simplify query logic for reporting teams

Some systems use materialized views (precomputed queries stored as tables) to balance normalization with performance — often refreshed daily or hourly.

• Keep normalized base tables for core operations
• Use denormalized views or tables only for fast access/reporting
• Document where data is duplicated to avoid confusion
• Schedule updates to sync denormalized data regularly

• Denormalized student_report table for parent reports
• Exam performance table with pre-joined subject, student, and teacher info
• Attendance summary tables with student name, class, and monthly percentage

Fewer joins do not automatically imply faster overall behavior. Extra writes, inconsistent copies and refresh delays are costs. Regular views do not store result snapshots; materialized-view support and refresh mechanisms differ by engine.

Create a class summary snapshot, then explain what must happen after another student joins.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

CREATE TABLE practice_class_summary AS SELECT class, COUNT(*) AS students FROM practice_students GROUP BY class;
SELECT class, students FROM practice_class_summary ORDER BY class;

The summary stores the counts from its creation time. Later source changes will not automatically update this ordinary snapshot table; a maintenance or rebuild policy is required.

Denormalization deliberately duplicates or precomputes selected information to serve a measured read requirement, with an explicit policy for keeping that information correct.

The summary stores the counts from its creation time. Later source changes will not automatically update this ordinary snapshot table; a maintenance or rebuild policy is required.

Continue your understanding with Views and Tune SQL Performance and Transactions.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.