Denormalization

Every step of normalization moved in the same direction: remove repetition, split the relation, enforce the rule in the schema. The cost mentioned at the end of the previous lesson is measured here — every split relation must be joined back together at read time. This lesson’s question is: how is that cost counted, and under what condition is putting repetition back deliberately defensible?

Denormalization is deliberately adding repetition to a normalized schema for read performance. Two words in the definition are decisive: deliberately (by measurement, not by mistake) and to a normalized schema (never normalizing at all is not denormalization; it is an incomplete design).

The Cost of Reading

Displaying a loan list on screen requires the member name, the book title, and the branch name. In a normalized schema this information sits in four separate relations. The number of relations the engine accesses while executing the query can be counted from the plan output:

sqlite3 :memory: <<'SQL'
CREATE TABLE branch (branch_code TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL);
CREATE TABLE member (member_no INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE book   (isbn TEXT PRIMARY KEY NOT NULL, title TEXT NOT NULL);
CREATE TABLE loan (
  loan_no       INTEGER PRIMARY KEY,
  member_no     INTEGER NOT NULL REFERENCES member (member_no),
  isbn          TEXT    NOT NULL REFERENCES book (isbn),
  branch_code   TEXT    NOT NULL REFERENCES branch (branch_code),
  checkout_date TEXT    NOT NULL
);
CREATE TABLE loan_summary (
  loan_no       INTEGER PRIMARY KEY,
  member_name   TEXT NOT NULL,
  book_title    TEXT NOT NULL,
  branch_name   TEXT NOT NULL,
  checkout_date TEXT NOT NULL
);
EXPLAIN QUERY PLAN
SELECT u.name, k.title, s.name, o.checkout_date
FROM loan o
JOIN member u ON u.member_no   = o.member_no
JOIN book   k ON k.isbn        = o.isbn
JOIN branch s ON s.branch_code = o.branch_code
WHERE o.checkout_date >= '2025-03-01';
EXPLAIN QUERY PLAN
SELECT member_name, book_title, branch_name, checkout_date
FROM loan_summary WHERE checkout_date >= '2025-03-01';
SQL

QUERY PLAN
|--SCAN o
|--SEARCH u USING INTEGER PRIMARY KEY (rowid=?)
|--SEARCH k USING INDEX sqlite_autoindex_book_1 (isbn=?)
`--SEARCH s USING INDEX sqlite_autoindex_branch_1 (branch_code=?)
QUERY PLAN
`--SCAN loan_summary

The first plan has four access steps, the second has one. The format of the plan output and the way the plan is requested vary by engine; what is counted is independent of format — how many relations the query touches, and by what path it accesses each one. The loan table is scanned, and the remaining three are accessed one row at a time through a key. That is three extra lookups per scan; on a million-row scan, three million lookups.

The cost of joins is not always this obvious. Access through a key is cheap, and small relations stay in memory. Moving to denormalization without measuring is the decision this lesson does not defend.

The Cost of Writing

When repetition is put back, the cost moves. When a branch’s name changes, a normalized schema updates one row; a summary table updates every row belonging to that branch:

sqlite3 :memory: <<'SQL'
.headers on
.mode box
CREATE TABLE branch (branch_code TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL);
CREATE TABLE loan_summary (loan_no INTEGER PRIMARY KEY, branch_code TEXT NOT NULL,
                         branch_name TEXT NOT NULL);
INSERT INTO branch VALUES ('CEN', 'Central'), ('BHC', 'Bahcelievler');
INSERT INTO loan_summary VALUES (1001, 'CEN', 'Central'), (1002, 'CEN', 'Central'),
                              (1003, 'BHC', 'Bahcelievler'), (1004, 'CEN', 'Central'),
                              (1005, 'CEN', 'Central');

UPDATE branch SET name = 'Central Branch' WHERE branch_code = 'CEN';
SELECT changes() AS source_rows;

UPDATE loan_summary SET branch_name = 'Central Branch' WHERE branch_code = 'CEN';
SELECT changes() AS summary_rows;

INSERT INTO loan_summary VALUES (1006, 'CEN', 'Central');
SELECT branch_code, COUNT(DISTINCT branch_name) AS distinct_names FROM loan_summary GROUP BY branch_code;
SQL

┌─────────────┐
│ source_rows │
├─────────────┤
│ 1           │
└─────────────┘
┌──────────────┐
│ summary_rows │
├──────────────┤
│ 4            │
└──────────────┘
┌─────────────┬────────────────┐
│ branch_code │ distinct_names │
├─────────────┼────────────────┤
│ BHC         │ 1              │
│ CEN         │ 2              │
└─────────────┴────────────────┘

Four rows were updated instead of one — a measurable and acceptable cost. The real danger is in the final statement: a new row was inserted into the summary table under the old name, and the branch again appears under two names. The cost of denormalization is not the update cost, it is the obligation of every write path to fill in the copy correctly.

Derived Columns and Every Write Path

The same problem appears more sharply with derived values. Holding a member’s open-loan count in a column instead of counting it on every query moves the cost of counting to write time. Consistency can be maintained with triggers — as long as every write path is covered:

sqlite3 :memory: <<'SQL'
.headers on
.mode box
CREATE TABLE member (member_no INTEGER PRIMARY KEY, name TEXT NOT NULL,
                    open_loans INTEGER NOT NULL DEFAULT 0);
CREATE TABLE loan (loan_no INTEGER PRIMARY KEY, member_no INTEGER NOT NULL,
                    return_date TEXT);
INSERT INTO member VALUES (41, 'Alice Kane', 0), (52, 'Marcus Reyes', 0);

CREATE TRIGGER loan_inserted AFTER INSERT ON loan
BEGIN
  UPDATE member SET open_loans = open_loans + 1 WHERE member_no = NEW.member_no;
END;
CREATE TRIGGER loan_returned AFTER UPDATE OF return_date ON loan
WHEN OLD.return_date IS NULL AND NEW.return_date IS NOT NULL
BEGIN
  UPDATE member SET open_loans = open_loans - 1 WHERE member_no = NEW.member_no;
END;

INSERT INTO loan VALUES (1001, 41, NULL), (1002, 41, NULL), (1003, 52, NULL);
UPDATE loan SET return_date = '2025-03-16' WHERE loan_no = 1002;
SELECT u.member_no, u.open_loans AS counter,
       (SELECT COUNT(*) FROM loan o
         WHERE o.member_no = u.member_no AND o.return_date IS NULL) AS actual
FROM member u;

DELETE FROM loan WHERE loan_no = 1001;
SELECT u.member_no, u.open_loans AS counter,
       (SELECT COUNT(*) FROM loan o
         WHERE o.member_no = u.member_no AND o.return_date IS NULL) AS actual
FROM member u;
SQL

┌───────────┬─────────┬────────┐
│ member_no │ counter │ actual │
├───────────┼─────────┼────────┤
│ 41        │ 1       │ 1      │
│ 52        │ 1       │ 1      │
└───────────┴─────────┴────────┘
┌───────────┬─────────┬────────┐
│ member_no │ counter │ actual │
├───────────┼─────────┼────────┤
│ 41        │ 1       │ 0      │
│ 52        │ 1       │ 1      │
└───────────┴─────────┴────────┘

The first result is correct because the insertion and return paths are covered. No trigger was written for the deletion path; when a loan record is deleted, the counter stays where it was and diverges from the value actually counted. This is the rule for derived columns: every path that changes the source must be counted — insertion, deletion, updates, bulk migrations, and manually run maintenance statements included. If one path is skipped, the column silently drifts into being wrong, and when the wrongness began does not appear in the records.

This is why derived values need a second requirement: an audit job that recomputes from the source and reports the difference. The second query above is exactly that.

Forms of Denormalization

Five patterns appear in practice, and their costs differ.

• Copied column. A frequently read attribute is copied into the referencing relation. Cheap if the source rarely changes.
• Derived total. A count, a sum, or a most-recent date is kept in a column. This is the example above; every write path must be covered.
• Summary relation. A precomputed relation is kept for reporting and refreshed at set intervals. It sets up an explicit trade-off between freshness and cost.
• Pre-joined relation. The result of a frequently used join is stored.
• View. The join is moved behind a view; because the result is not stored, no repetition is added — only the query text becomes simpler. Whether a stored form of the result exists, and how it is refreshed, varies by engine.

The last item matters: a view alone is not denormalization. The read cost stays the same; only the repeated query text disappears.

Decision Criteria

Denormalization is a last-resort tool. What is tried before it is well established: fixing the query itself, adding the necessary indexes, cutting unnecessary column reads, using pagination. These leave the data in one place; denormalization does not.

Four questions are asked for the decision. What is the ratio of reads to writes — if reads do not dominate, the gain is small and the cost is large. How often does the copied data change — frequently changing data constantly invalidates the copy. Is stale data acceptable — this is the most critical answer to give for a summary relation. And are all the paths that fill the copy known — if not, the decision should be deferred.

The decision itself must also remain in writing. Someone reading the schema later may mistake the repetition for a design error and try to “fix” it; keeping a record of which measurement made the repetition necessary prevents that.

Summary

• Denormalization is adding repetition to a normalized schema based on measurement; never normalizing at all is not denormalization.
• The read cost is measured by counting, from plan output, how many relations a query accesses and by what path.
• When repetition is added, the cost moves to write time: the number of updated rows grows, and every write path must fill the copy correctly.
• A derived column’s consistency silently breaks when one of the paths that changes its source is skipped; an audit job that recomputes from the source is required.
• Query fixes, index additions, and pagination are tried before denormalization; the decision is left in writing along with its justification.

Next Step

The final lesson of the normalization topic moves past individual rules to look at recurring design situations: how one-to-many and many-to-many relationships are built, how tree-shaped data is held in a relational schema, how a row is versioned when history must be preserved, and which common patterns are actually anti-patterns — tables that store every row as a key–value pair lead this list.