Schema design is mostly a sequence of small, reversible decisions and a handful of expensive, hard-to-reverse ones. Knowing which is which up front saves a lot of migration pain later.
1. Start from the entities, not the tables
Before writing any CREATE TABLE, list the real-world things your application
deals with — customer, order, product — as nouns, independent of how they'll be stored.
Each becomes a candidate table. Relationships between them (a customer has many
orders; an order has many products, and a product appears in many orders) tell you
where foreign keys and join tables go.
2. Normalize until duplication hurts to keep
Normalization is often taught as a series of numbered forms (1NF, 2NF, 3NF), but the practical version is simpler: each fact should live in exactly one place. If a product's price is copied into every order-line row, a price change means updating every historical order — which is sometimes actually what you want (an order should reflect the price paid, not today's price), but that's a deliberate choice, not an accident of copy-pasted columns.
-- Normalized: price looked up from products at read time
CREATE TABLE order_items (
order_id INTEGER REFERENCES orders(id),
product_id INTEGER REFERENCES products(id),
quantity INTEGER NOT NULL
);
-- Deliberately denormalized: price captured at time of sale
CREATE TABLE order_items (
order_id INTEGER REFERENCES orders(id),
product_id INTEGER REFERENCES products(id),
quantity INTEGER NOT NULL,
unit_price_cents INTEGER NOT NULL -- snapshot, not a live lookup
);3. Choose primary keys deliberately
An auto-incrementing integer (SERIAL / IDENTITY in Postgres,
INTEGER PRIMARY KEY in SQLite) is compact, fast to index, and easy to reason
about. A UUID is unguessable and can be generated before the row is inserted — useful for
client-generated IDs or merging data from multiple sources — at the cost of a larger index
and no natural sort order. Neither is universally right; the wrong one to pick is "whatever
the framework defaults to, without thinking about it."
4. Decide foreign-key behavior up front
What happens to order_items when an order is deleted? This is
the kind of decision that's easy to get wrong quietly and painful to fix once you have
production data depending on it.
CREATE TABLE order_items (
order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
product_id INTEGER REFERENCES products(id) ON DELETE RESTRICT,
quantity INTEGER NOT NULL
);ON DELETE CASCADE removes dependent rows automatically —fine for
order-to-order-items, since a deleted order shouldn't leave orphaned line items.
ON DELETE RESTRICT blocks the delete instead — better for
product-to-order-items, since deleting a product shouldn't silently erase historical order
records.
5. Index for the queries you'll actually run
A primary key is indexed automatically; foreign keys and columns you filter or sort by often need an explicit index. The trade-off is real: every index speeds up reads on that column but slows down writes and takes up space. Add indexes based on the queries your application runs, not speculatively for every column.
CREATE INDEX idx_orders_customer_id ON orders(customer_id);What's expensive to change later
- Primary key type — switching from integer to UUID after other tables reference it means rewriting every foreign key.
- A column's fundamental type — going from a string to a proper
DATEorENUMafter production data already violates the new constraint requires a data-cleanup migration first. - Splitting a table in two once queries and application code assume a single wide table — doable, but every read and write path needs updating together.
What's cheap: adding a nullable column, adding an index, adding a new table. Design around the expensive decisions; iterate freely on the cheap ones.
A visual schema diagram makes a lot of these relationships obvious before you commit to them. QuerySQL's schema diagram view lays out tables by foreign-key dependency, with delete-rule labels on every relationship line.