Normalization & Denormalization Avoid duplicated truth without turning the schema into theory theater
Phase 10 - Relational Data Modeling Volume 03 - Database Engineering
↓
↓
Phase 10 decides what the durable data should mean before Phase 11 writes application behavior against it.
What normalization is trying to prevent Normalization reduces contradictory copies of the same fact. If workspace name is repeated on every task, renaming the workspace requires changing many rows and creates opportunities for inconsistency.
↓
↓
↓
Relationships replace duplicated facts with references to authoritative records.
Practical normal form mindset Question Healthy direction
Does each row represent one thing? Yes - avoid repeating groups
Does an attribute depend on the whole identity? Yes - avoid partial dependencies
Is a fact stored in the entity that owns it? Yes - avoid transitive duplication
Do not over-normalize labels into pointless tables A three-value stable priority does not automatically need a task_priorities table. Normalization is about dependency and truth, not maximizing table count.
When denormalization can be intentional Read-heavy reporting, immutable historical snapshots, analytics, search documents, or expensive aggregate views can justify duplicated derived data. But denormalization should name the consistency strategy explicitly. Never duplicate a field because JOINs "feel slow" without measurement. Relational databases are designed to join normalized data efficiently when keys and indexes are appropriate.
TaskFlow examples • Workspace name belongs on Workspace, not Task. • Membership role belongs on Membership, not globally on User. • Task status belongs on Task because it is a fact about that task.
• A future immutable audit event may snapshot labels because history should not change when the live record changes.
Normalization and Denormalization matters because it changes a real boundary in TaskFlow. The goal is to understand the inputs, owner, observable output, and failure modes before adding more abstraction.
Restate the goal of “Normalization and Denormalization” as one observable behavior. Write the success signal before touching code.
Trace the boundary map: business noun → invariant → table/relation → constraint → query shape. Mark the one boundary this lesson changes.
Inspect the current repository state and record the exact files, route, table, or runtime involved. Avoid creating an abstraction before you can name the existing owner.
Implement the smallest vertical slice. Add one coherent change, run the narrowest verification command, and read the output before continuing.
Connect the slice to TaskFlow’s phase artifact: A complete Prisma/ER model for TaskFlow core entities with keys, relation tables, uniqueness, and important indexes.. Verify behavior from the outside as a user or API client would.
Create one controlled failure that is safe to reproduce. Predict the symptom first, capture evidence, locate the earliest broken boundary, then fix only that cause.
Close the reference and rebuild or explain the core change from memory. Record what you would monitor or test before trusting the same change in production.
Input -> owner -> transformation -> observable output -> failure evidenceDo not copy this as a finished answer. Predict what each boundary does, type the smallest useful slice, run it, then compare the result with your prediction.
Return to the nearest observable boundary: input, network, process log, database, or rendered UI. Compare actual evidence with the predicted path.
Change one variable at a time and reproduce the original failure after the fix; otherwise you have correlation, not diagnosis.
Without copying the final source, create a second small scenario that applies “Normalization and Denormalization” to a different TaskFlow resource or input. Write the acceptance criteria first, implement it, and provide the evidence that each criterion is satisfied.
List TaskFlow nouns and separate entities from attributes and events.
Use the smallest change that demonstrates this lesson. Stop after it works and capture evidence.Duplicate membership rows permitted
Predict the symptom before causing the failure. Then locate the earliest boundary where reality diverges from expectation.Why does this responsibility live here, what input does it trust, what output proves success, and what would go wrong if another layer owned it?
If your explanation is vague, repeat the boundary trace before moving on.Close the reference. Reproduce the core idea from memory or explain the exact request/data flow aloud. Then check your diff or notes for accidental shortcuts that bypass validation, authorization, error handling, or verification earned in earlier phases.