Don't Panic: Relational Database Design

A relational database design is the part where ordinary business facts are persuaded to stop wandering around in columns and acquire an address. It decides what a row means, which facts belong together, and which combinations the database must reject. The table is only the visible furniture. The contract is the building.

Begin with grain, meaning the exact fact represented by one row. An order row is one order. An order-line row is one product on one order. This sounds modest until a product name, an order date, and a quantity all queue up asking for a home. Grain answers them before they form a small but determined committee in the wrong table.

Next come relationships and keys. A customer can have orders, an order can have lines, and a product can appear in many lines. The order-line table is not bureaucratic paperwork. It is where the relationship lives, along with facts such as quantity and agreed price. A primary key identifies a row; a foreign key says which related rows are permitted. A generated identifier can help with references, but it cannot magically make a duplicated product code unique.

Then make the important rules executable. NOT NULL, UNIQUE, foreign keys, and checks are the database's way of declining bad data with considerably more consistency than a diagram. Normalization helps place each fact with the key that determines it, so repeated copies do not develop independent opinions about a product name. It is not a contest to build the most tables. It is a way to find redundancy before redundancy finds production.

Physical choices arrive later, wearing more expensive shoes. Indexes can speed selected lookups, but also add storage and write work. Denormalization can serve a measured read need, but it needs a source of truth, a maintenance rule, and a repair path. A duplicate without those is not performance engineering. It is a future reconciliation task hiding under a useful-looking name.

Read the Intro for the full path from domain terms to constraints. Use Slides when the relationships and dependency questions need a compact map. Keep the Cheatsheet nearby when reviewing keys, normal forms, indexes, and failure signals. The Practice tab turns the ideas into a purchase-order schema, where invalid inserts have the useful habit of telling you exactly which rule you forgot to declare.

Don't Panic: Relational Database Design

Relevant careers