A good relational database is not drawn once and forgotten. It is worked out in a specific order: understand the data, name the things in it, connect them, then harden the result with keys, types, and constraints. This guide walks that whole process and builds one real schema, a small store, from a blank page to runnable CREATE TABLE statements.
The relational model, introduced by Edgar Codd in 1970, organises data into tables of rows and columns linked by shared values rather than by pointers. You can read the theory on the relational model reference, but design in practice is a sequence of concrete decisions. Follow them in order and the hard problems mostly disappear before you ever write a query.
What this guide covers
- Gather requirements first
- The design process, step by step
- Entities, attributes, and the ERD
- Relationships: 1:1, 1:many, many:many
- Primary keys and foreign keys
- Normalize, then pick types and constraints
- Naming conventions
- Indexes and where effort pays off
- The final schema
- A reusable design checklist
1. Gather requirements first
Before any table exists, you need to know what the system must remember and what questions it must answer. Skip this and you will design for the data you imagine instead of the data the business actually has. The goal is a short list of nouns (the things you store) and verbs (the things that happen to them).
For our worked example, imagine a small online store. Talking to the people who run it produces a handful of plain sentences: customers place orders; an order contains one or more products; products belong to a category; a customer can save several shipping addresses. Those sentences are the raw material for the whole schema. Write them down in the customer's own language before you translate anything into tables.
- What do we store? Customers, products, categories, orders, addresses.
- What must we answer? Revenue per month, top products, orders per customer, stock on hand.
- What are the rules? An order always belongs to exactly one customer; a product price must never be negative.
2. The design process, step by step
Database design is repeatable. Each step below feeds the next, and skipping one almost always shows up later as a migration. Work top to bottom the first time, then loop back as requirements sharpen.
Identify entities
Turn the nouns from your requirements into candidate tables: customer, product, category, order, address.
List attributes
For each entity, write the facts you must store: a customer has a name and an email; a product has a name, a price, and a stock count.
Define relationships
Decide how entities connect and with what cardinality: one customer to many orders, many products to many orders.
Choose primary keys
Give every table one stable way to identify a single row, usually a surrogate id.
Add foreign keys
Wire the relationships into the schema so the database, not the app, guarantees they stay valid.
Normalize
Remove repeated and derivable data so each fact lives in exactly one place, typically to third normal form.
Choose data types
Match each column to the smallest correct type: DATE for dates, DECIMAL for money, INT for counts.
Add constraints
Encode the business rules: NOT NULL, UNIQUE, CHECK, and default values.
Plan indexes
Index the columns you join and filter on so reads stay fast as the tables grow.
Name consistently
Apply one naming convention across every table and column so the schema reads itself.
3. Entities, attributes, and the ERD
An entity is a thing worth storing; an attribute is a fact about it. From our store we get five entities. The next move is to sketch how they relate before writing any DDL, and the standard tool for that is an entity relationship diagram, or ERD.
An ERD draws each entity as a box, lists its attributes, and connects the boxes with lines whose endpoints show cardinality (one, or many). Reading our store as an ERD in words: a customer box connects to address with a one to many line (one customer, many saved addresses) and to order with another one to many line. The order box connects to product through an order_item box in the middle, because an order holds many products and a product appears on many orders. A category box connects to product one to many. For a full walkthrough of the notation, see the guide on entity relationship diagrams.
Tip: draw the ERD before the DDL. Moving a box on a diagram costs a minute; moving a column in a live table costs a migration and a code change across the whole application.
4. Relationships: 1:1, 1:many, many:many
Every link between two entities has a cardinality, and each kind is implemented differently. Getting this right is the core of relational design, because the relationship decides where the foreign key lives and whether you need an extra table.
| Type | Store example | How to implement |
|---|---|---|
| One to one (1:1) | A user and their single login profile | Foreign key with a UNIQUE constraint, or merge into one table |
| One to many (1:many) | One customer, many orders | Foreign key on the "many" side (orders.customer_id) |
| Many to many (many:many) | Orders and products | A junction table (order_items) with two foreign keys |
The one to many case is the workhorse: put the foreign key on the child. An order belongs to one customer, so orders carries a customer_id. The one to one case is rarer; when you truly need it, add a foreign key and make it UNIQUE so a parent cannot have two children, or simply keep the columns in the same table.
Many to many cannot be expressed with a single foreign key, because each side has many of the other. The solution is a junction table (also called a bridge or link table) that holds one row per pairing. Our store needs one between orders and products, and that table is also the perfect place to store facts about the pairing itself, such as the quantity ordered and the price at the time of sale.
-- junction table resolves the many-to-many between orders and products
CREATE TABLE order_items (
order_id BIGINT NOT NULL REFERENCES orders(id),
product_id BIGINT NOT NULL REFERENCES products(id),
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (order_id, product_id)
);Notice the composite primary key on the two foreign keys: it guarantees the same product cannot appear twice on one order, and it doubles as the natural identity of the row. Storing unit_price here, rather than reading it live from products, keeps historical orders correct even after a product's price changes later.
5. Primary keys and foreign keys
A primary key is the one column (or set of columns) that uniquely identifies a row and never changes. A foreign key is a column that points at another table's primary key, and the database enforces that the target actually exists. Together they are the skeleton of the relational model. If the difference is still fuzzy, the deep dive on primary key versus foreign key is worth a read.
For most tables, prefer a surrogate key: an auto generated integer (BIGINT) with no business meaning. It stays stable even when real world values change, and it keeps foreign keys narrow and fast. Reserve natural keys, like an email or an SKU, for a UNIQUE constraint that enforces the real world rule without becoming the identity everything else depends on.
CREATE TABLE customers (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE, -- natural key as a constraint
name VARCHAR(120) NOT NULL
);The surrogate id is the identity; the UNIQUE email still blocks duplicate accounts. When another table needs to reference a customer, it copies that small integer id, never the long email, so joins stay cheap and references never break because someone changed their address.
6. Normalize, then pick types and constraints
Normalization is the discipline of removing redundancy so each fact is stored once. The practical target for a transactional store is third normal form: every non key column depends on the key, the whole key, and nothing but the key. That is exactly why the customer's name lives in customers and not repeated on every order row. The full ruleset, with worked examples, is in the database normalization guide and the companion article on normalization explained.
Once the shape is right, give every column the smallest correct data type. Types are free validation: they reject impossible values, sort correctly, and store compactly. The reference on data definition in the PostgreSQL DDL documentation is an excellent map of what real engines offer.
| Data | Right type | Why |
|---|---|---|
| Surrogate key | BIGINT identity | Never runs out; narrow for joins |
| Money | DECIMAL(10,2) | Exact to the cent, no float rounding |
| Dates and times | DATE, TIMESTAMP | Real date math and correct sorting |
| Counts and quantities | INT | Aggregates like SUM just work |
| Short labels | VARCHAR(n) | Bounded length documents intent |
| Flags | BOOLEAN | Two clear states, not a nullable text |
Constraints then encode the business rules directly in the schema so no application bug can violate them. Use NOT NULL for anything mandatory, UNIQUE for natural keys, CHECK for value rules (a price is never negative), and DEFAULT for sensible fallbacks. A rule enforced by the database holds for every writer: the web app, the admin script, and the 3am hotfix alike.
Watch out: never store money as FLOAT. Binary floating point cannot represent most decimal fractions exactly, so totals drift by pennies that will not reconcile. Always reach for DECIMAL or NUMERIC for currency.
7. Naming conventions
Consistent names are not decoration. When one table says userid, another says user_ID, and a third says fk_user, every join becomes a guessing game. Pick one convention up front and apply it to every object. The rules below are a solid default that works across MySQL, PostgreSQL, and SQL Server.
| Object | Convention | Example |
|---|---|---|
| Tables | snake_case, plural nouns | order_items |
| Columns | snake_case, singular | unit_price |
| Primary key | always id | id |
| Foreign key | singular table plus _id | customer_id |
| Booleans | is_ or has_ prefix | is_active |
| Indexes | idx_table_columns | idx_orders_customer_id |
Two extra habits pay off: avoid reserved words such as order, user, and type as bare column names so you never have to quote them, and stick to snake_case so identifiers survive case insensitive engines without surprises.
8. Indexes and where effort pays off
Indexes make reads fast by letting the engine jump straight to matching rows instead of scanning the whole table. Index the primary key (done for you), every foreign key you join on, and the columns that appear in your common WHERE clauses. Do not index everything: each index must be maintained on every write, so unused ones are pure cost.
-- index the foreign keys and the columns reports filter on
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_products_category_id ON products(category_id);
CREATE INDEX idx_orders_placed_at ON orders(placed_at);It helps to know which design steps return the most value per hour spent. The chart below is a representative view: the decisions that shape your data, keys and relationships and types, are cheap to make now and brutally expensive to change later, while indexes can be added at any time. Spend your care where it compounds.
Read it as a schedule of regret, not a ranking of importance. Indexing scores lower here only because it is easy to fix on a live system; the structural choices score high because getting them wrong means migrating every existing row and rewriting every query that touched the old shape.
9. The final schema
Putting every decision together produces the complete store schema. Read it top to bottom: parent tables first (so foreign keys can reference them), then children, then the junction table. Every table has a surrogate id, foreign keys are declared and indexed, money is DECIMAL, and constraints carry the business rules.
CREATE TABLE categories (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(80) NOT NULL UNIQUE
);
CREATE TABLE customers (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(120) NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE addresses (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
line1 VARCHAR(160) NOT NULL,
city VARCHAR(80) NOT NULL,
postal_code VARCHAR(20) NOT NULL
);
CREATE TABLE products (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
category_id BIGINT NOT NULL REFERENCES categories(id),
sku VARCHAR(40) NOT NULL UNIQUE,
name VARCHAR(160) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock_qty INT NOT NULL DEFAULT 0
);
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
status VARCHAR(20) NOT NULL DEFAULT 'pending',
placed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE order_items (
order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id BIGINT NOT NULL REFERENCES products(id),
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (order_id, product_id)
);With that schema in place, the reporting questions from step one become clean, indexed queries. Revenue and best sellers both fall out of a single join across orders and order_items.
-- top products by revenue this year
SELECT p.name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN orders o ON o.id = oi.order_id
WHERE o.placed_at >= '2026-01-01'
GROUP BY p.name
ORDER BY revenue DESC
LIMIT 10;Because each fact lives in one place and every relationship is a real foreign key, that query cannot silently drop rows or double count. The design did the hard part; the SQL is almost boring, which is exactly the goal.
10. A reusable design checklist
The same process scales from this five table store to systems with hundreds of tables. Larger schemas follow identical rules, as covered in the guide on building enterprise databases with SQL. Before you run CREATE TABLE on anything real, walk this list one more time.
- Requirements captured in plain sentences, with the questions the data must answer.
- Entities and attributes mapped, one entity per table, one fact per column.
- Relationships classified as 1:1, 1:many, or many:many, with junction tables where needed.
- Keys in place: a surrogate primary key everywhere, foreign keys on every relationship.
- Normalized to third normal form, denormalizing only where a benchmark proves the need.
- Types and constraints chosen deliberately, with money as
DECIMALand mandatory columnsNOT NULL. - Indexes on foreign keys and common filters; names consistent across the whole schema.
Design is a skill you build by doing it, then reviewing the result. If you want structured practice with keys, joins, and schema work, the SQL intermediate course takes you from a blank page to a production ready model with exercises at every step.