Chapter 12 · Normalization and Practical Schema Design
Denormalization and Read-Optimized Models
Normalization protects correctness; denormalization spends controlled redundancy to improve a measured workload. The decision is safe only when ownership, refresh, validation, and recovery rules are explicit.
Learning outcomes
Distinguish accidental redundancy from deliberate denormalization.
Quantify read benefit, write amplification, freshness lag, and rebuild cost.
Choose among cached columns, summary tables, snapshots, and dimensional models.
Implement and verify a read-optimized aggregate table in SQLite.
Design ownership and reconciliation controls that keep duplicated facts trustworthy.
Denormalization is a controlled duplication
A denormalized value is intentionally stored even though it can be derived from authoritative normalized data. The design is justified only when it serves a measured access pattern and has a defined maintenance contract.
Authoritative fact
One normalized relation owns the business truth.
Read model
A duplicated or aggregated representation serves a specific query workload.
Freshness contract
Readers know whether the copy is synchronous, near-real-time, or batch-refreshed.
Recovery path
The read model can be recreated and reconciled from the source of truth.
Before denormalizing
Denormalization should follow simpler, lower-risk optimizations rather than replace them.
Reusable normalized target schema
This design separates facts by grain: one row per student, instructor, course, offering, and enrollment.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS enrollment;DROP TABLE IF EXISTS course_offering;DROP TABLE IF EXISTS course;DROP TABLE IF EXISTS instructor;DROP TABLE IF EXISTS student;CREATE TABLE student ( student_id INTEGER PRIMARY KEY, student_name TEXT NOT NULL, student_email TEXT NOT NULL UNIQUE) STRICT;CREATE TABLE instructor ( instructor_id INTEGER PRIMARY KEY, instructor_name TEXT NOT NULL) STRICT;CREATE TABLE course ( course_id INTEGER PRIMARY KEY, course_code TEXT NOT NULL UNIQUE, course_title TEXT NOT NULL) STRICT;CREATE TABLE course_offering ( offering_id INTEGER PRIMARY KEY, course_id INTEGER NOT NULL REFERENCES course(course_id), instructor_id INTEGER NOT NULL REFERENCES instructor(instructor_id), term_code TEXT NOT NULL, room_code TEXT, UNIQUE (course_id, term_code)) STRICT;CREATE TABLE enrollment ( offering_id INTEGER NOT NULL REFERENCES course_offering(offering_id) ON DELETE CASCADE, student_id INTEGER NOT NULL REFERENCES student(student_id) ON DELETE CASCADE, grade TEXT CHECK (grade IS NULL OR grade IN ('A','A-','B+','B','B-','C+','C','D','F')), PRIMARY KEY (offering_id, student_id)) STRICT, WITHOUT ROWID;A normalized read path
SELECT o.offering_id, c.course_code, c.course_title, COUNT(e.student_id) AS enrollment_count, SUM(CASE WHEN e.grade IS NOT NULL THEN 1 ELSE 0 END) AS graded_countFROM course_offering AS oJOIN course AS c ON c.course_id = o.course_idLEFT JOIN enrollment AS e ON e.offering_id = o.offering_idGROUP BY o.offering_id, c.course_code, c.course_titleORDER BY o.offering_id;This query is correct and often fast enough with appropriate keys. A summary table becomes reasonable only when the aggregation is expensive, requested frequently, and allowed to have a declared freshness policy.
Materialized summary table
DROP TABLE IF EXISTS offering_metrics;CREATE TABLE offering_metrics ( offering_id INTEGER PRIMARY KEY, enrollment_count INTEGER NOT NULL, graded_count INTEGER NOT NULL, refreshed_at TEXT NOT NULL) STRICT;DELETE FROM offering_metrics;INSERT INTO offering_metrics ( offering_id, enrollment_count, graded_count, refreshed_at)SELECT o.offering_id, COUNT(e.student_id), SUM(CASE WHEN e.grade IS NOT NULL THEN 1 ELSE 0 END), CURRENT_TIMESTAMPFROM course_offering AS oLEFT JOIN enrollment AS e ON e.offering_id = o.offering_idGROUP BY o.offering_id;The table stores derived facts. Its owner is the refresh process—not an arbitrary application writer.
Verify the copy against the source
WITH authoritative AS ( SELECT o.offering_id, COUNT(e.student_id) AS enrollment_count, SUM(CASE WHEN e.grade IS NOT NULL THEN 1 ELSE 0 END) AS graded_count FROM course_offering AS o LEFT JOIN enrollment AS e ON e.offering_id = o.offering_id GROUP BY o.offering_id)SELECT a.offering_id, a.enrollment_count AS expected_enrollment_count, m.enrollment_count AS stored_enrollment_count, a.graded_count AS expected_graded_count, m.graded_count AS stored_graded_countFROM authoritative AS aLEFT JOIN offering_metrics AS m ON m.offering_id = a.offering_idWHERE m.offering_id IS NULL OR a.enrollment_count <> m.enrollment_count OR a.graded_count <> m.graded_count;A trustworthy read model has automated reconciliation and a rebuild path. Empty results mean the copy currently agrees with the source.
Synchronous versus asynchronous maintenance
| Strategy | Freshness | Write cost | Failure mode |
|---|---|---|---|
| Generated column | Same row, immediate | Computed on every write | Limited to values derivable from the same row. |
| Trigger-maintained summary | Immediate transactionally | Higher write complexity and contention | Buggy trigger can corrupt the copy. |
| Application dual write | Potentially immediate | Coordination across code paths | Partial failure causes divergence. |
| CDC / event projection | Near-real-time | Asynchronous infrastructure | Lag, replay, and ordering must be managed. |
| Scheduled rebuild | Batch freshness | Cheap source writes | Readers see stale data between refreshes. |
Useful denormalization patterns
| Pattern | Use case | Control |
|---|---|---|
| Cached display label | Avoid repeated joins for immutable historical presentation. | Store as a snapshot and name it clearly. |
| Aggregate table | Dashboards and repeated group calculations. | Refresh timestamp, reconciliation, and rebuild command. |
| Event snapshot | Preserve the state seen when a transaction occurred. | Treat the snapshot as historical fact, not current master data. |
| Star schema | Analytical scans by dimensions and measures. | Document grain, slowly changing dimensions, and load process. |
| Search document | Full-text or faceted retrieval across several entities. | Reindex and compare source version or event offset. |
Star schema example
fact_enrollment( enrollment_key, student_key, course_key, instructor_key, term_key, enrolled_count, completed_count)dim_student(student_key, student_id, region, valid_from, valid_to)dim_course(course_key, course_id, code, title, category)dim_instructor(instructor_key, instructor_id, name)dim_term(term_key, term_code, year, season)A star schema is intentionally not the same as the operational 3NF model. Its grain and duplication are optimized for analytical grouping and history.
A decision equation
The quantities need not be monetary, but they should be observable: query latency, CPU, writes per transaction, maximum lag, rebuild duration, and incident risk.
Checkpoint
Approve the read model
- What distinguishes denormalization from accidental redundancy?
- Why should indexing be evaluated first?
- Who should own a summary table?
- What evidence proves a read model is trustworthy?
- Why is a historical snapshot not necessarily a violation of normalization?
Review the answers
Denormalization is deliberate, workload-driven, and controlled. Indexes often solve performance without duplicating facts. A defined refresh process owns the summary. Reconciliation, freshness, and rebuild evidence establish trust. A snapshot records a new historical fact—the value at an event time—rather than claiming to be current master data.
Summary and references
- Normalize first, then denormalize only for a measured workload.
- Every duplicate needs an authoritative owner and freshness contract.
- Summary tables, snapshots, star schemas, and search projections serve different read patterns.
- Reconciliation and rebuildability are mandatory reliability controls.
- Read benefit must exceed write, staleness, and operational costs.