--- title: Denormalization source: 'https://academia.sh/en/courses/relational-theory/denormalization' course: 'Data Modeling and Relational Theory' language: en updated: '2026-08-23T07:00:47+00:00' license: 'CC BY-SA 4.0' --- # Denormalization Measuring the read cost of a normalized schema, the write cost of denormalization, the requirement that a derived column cover every write path, and the criteria for a denormalization decision. 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: ```sh 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: ```sh 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: ```sh 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.