Dev Basics · 5 min read
Database Schema Design: Tables, Keys, and Normalization
A good schema makes queries simple and data trustworthy; a poor one creates duplicated, contradictory data that no amount of application code can fully clean up. A few core ideas cover most designs.
Try it yourself with the related tool.
Design a database schema →Advertisement
Entities and keys
Each table represents one kind of thing — users, orders, products. Every table needs a primary key that uniquely identifies a row; a surrogate key such as an auto-increment integer or a UUID is usually easier than a natural key like an email address, which can change.
Relationships
A one-to-many relationship puts a foreign key on the "many" side: each order has a user_id referencing users.id. A many-to-many relationship, like students and courses, needs a join table with two foreign keys. Foreign key constraints let the database refuse orphans — an order for a user that does not exist.
Normalization in plain words
Normalization means storing each fact once. First normal form: no lists crammed into a single column. Second and third: every column describes the whole key and nothing but the key — so a customer's address lives in the customers table, not copied into every order. When a fact lives in one place, it cannot contradict itself.
When to break the rules
Sometimes you deliberately copy data: an order should keep the price paid even if the product price changes later. Reporting tables may be denormalized for speed. The point is to duplicate on purpose, not by accident. Draw the schema as an ER diagram before writing migrations — mistakes are cheapest to fix on paper.