FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 Normalization and Schema Evolution
Normalization and Schema Evolution Design durable facts and change schemas without losing the mental model
Persistence becomes understandable when the database stops being magic.
understand the database before Prisma
Figure - Phase 07 isolates PostgreSQL fundamentals so later ORM code has a clear relational meaning.
FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 Normalization and Schema Evolution
task row repeats project name projects, users, tasks and owner email linked by keys
store each durable fact in a sensible place; join related facts when reading
Figure - Normalization reduces contradictory copies of the same fact; it is a design tool, not a rule to maximize table count.
Suppose every task row stores project_name and project_owner_email. Renaming the project would require updating many task rows; missing one creates contradictory copies of the same fact. A normalized model stores the project facts once and lets tasks reference the project.
The useful idea behind 1NF, 2NF and 3NF Form Practical intuition
1NF Store values in a queryable table shape; avoid repeating groups packed into one column.
2NF When a key has multiple columns, non-key facts should depend on the whole key.
3NF Non-key facts should describe the row identified by the key, not another non-key fact.
You do not need to chant normal forms while coding. The professional habit is to ask: What fact does this row represent? Where is that fact authoritative? What contradictions become possible if I duplicate it?
Denormalization is a trade-off, not a failure Sometimes duplicated or precomputed data is deliberately introduced for performance, reporting or historical snapshots. That is a design decision with synchronization costs. First understand the normalized source of truth; then denormalize when a measured requirement justifies it.
Schema evolution is inevitable Production schemas change as requirements change. PostgreSQL supports ALTER TABLE operations, but application teams normally record schema changes as versioned migrations so environments can advance in a repeatable order. Phase 09 shows how Prisma migrations become that change history for TaskFlow. Schema evolution examples
ALTER TABLE tasks ADD COLUMN due_at timestamptz;
ALTER TABLE tasks ADD CONSTRAINT tasks_title_not_blank CHECK (length(btrim(title)) > 0);
FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 Normalization and Schema Evolution
A migration is not just the final schema The final schema answers what the database looks like now. Migration history answers how an existing database gets from an older valid state to the new valid state. Those are different concerns.
Renames, destructive changes and data backfills ● Adding a nullable column is usually easier than immediately adding a required column to a table that already contains rows. ● Changing a column type can require conversion and may lock or rewrite data depending on the operation. ● Dropping a column destroys stored information; production migrations need backups, rollout planning and application compatibility. ● Large data backfills are operational work, not merely schema syntax. ● Application deployment and database migration order can matter when old and new application versions overlap.
Prisma will later generate migration SQL for many schema changes, but generated SQL is still database change code. A professional developer reviews what it will do instead of treating migrations as a magic button.
ORMs do not remove the database. SQL literacy lets you reason about correctness, constraints, query cost, transactions, and the evidence stored in PostgreSQL.
Restate the goal of “Normalization and Schema Evolution” as one observable behavior. Write the success signal before touching code.
Trace the boundary map: application intent → SQL statement → query planner → table/index → rows returned. 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 TaskFlow relational sketch plus SQL exercises that create, query, join, update, and constrain representative data.. 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.
SELECT t.id, t.title, u.name AS assignee
FROM tasks AS t
LEFT JOIN users AS u ON u.id = t.assignee_id
WHERE t.workspace_id = $1
ORDER BY t.created_at DESC, t.id DESC;Do 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.
Capture EXPLAIN ANALYZE, row counts, filters, sort keys, and indexes before adding an index.
Inspect join cardinality and constraints; do not hide a modeling bug with DISTINCT until you understand it.
Without copying the final source, create a second small scenario that applies “Normalization and Schema Evolution” to a different TaskFlow resource or input. Write the acceptance criteria first, implement it, and provide the evidence that each criterion is satisfied.
Create an index for a real filter/sort pattern and compare EXPLAIN output conceptually.
Use the smallest change that demonstrates this lesson. Stop after it works and capture evidence.Missing composite uniqueness on membership-like tables
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.