From Schema to ProductionLesson 6.5
Capstone: designing a complete schema from scratch
end-to-end design workflow, requirements analysis, ER diagram, normalization pass, constraints and types, indexing strategy, migration files, roles, documentation review
The Full Design Process
Good database design is a process, not a single step. Here is the complete workflow you now have the tools to execute.
Step-by-Step
- Gather requirements — write them down in plain English. Identify every noun and relationship.
- Draw the ER diagram — entities, attributes, cardinality. Use dbdiagram.io or draw.io. Get stakeholder sign-off before writing SQL.
- Normalize to 3NF — check every table for 1NF/2NF/3NF violations. Split where needed.
- Choose data types and constraints — NOT NULL, UNIQUE, CHECK, DEFAULT, FK with correct ON DELETE behavior.
- Plan indexes — every FK, every high-cardinality WHERE column, every sort column on large tables.
- Write migrations — one file per logical change, named and versioned.
- Apply roles — create app_user, report_user, migration_user with minimum required privileges.
- Document — inline SQL comments on non-obvious columns, a README for major decisions.
The Design Mindset
Every schema decision is a tradeoff. Normalization reduces redundancy but adds JOINs. Denormalization speeds reads but risks inconsistency. Soft deletes preserve history but grow tables. There is no universally correct schema — there is only the schema that correctly models your specific domain with the performance characteristics your application requires. Now you can make those tradeoffs deliberately.