What is it? #
Real data is connected. Customers place orders, orders contain products, users have roles. How you represent those connections is most of what database design actually is.
There are only three shapes, and every model you will ever build is made of them:
One-to-one — one row here matches at most one row there. A user and their profile.
One-to-many — one row here matches many rows there. One customer, many orders. This is by far the most common.
Many-to-many — many on both sides. A user can have several roles; a role belongs to several users. This one cannot be done with a single column, and needs a third table.
Learn to recognise which shape you are looking at and the tables write themselves.
Think of it like this #
Think about a school.
Each student has exactly one enrolment record — one-to-one.
Each class has many students, but each student is in one class at a time — one-to-many. The link lives on the "many" side: each student's record notes their class.
Each student studies many subjects, and each subject is studied by many students — many-to-many. You cannot write that on either card without running out of room, so the school keeps a separate register: one line per student-subject pairing. That register is a junction table.
Simple example #
An online shop needs: customers who place orders (one-to-many), orders that contain many products while each product appears in many orders (many-to-many), and staff users who hold roles, where each role grants several permissions (many-to-many twice over).
Five entities, three relationship shapes, and two junction tables.
Code #
---------- One-to-many: the most common shape ----------
customers orders
+----+------------+ +----+-------------+--------+
| id | name | | id | customer_id | total |
+----+------------+ +----+-------------+--------+
| 1 | Asha |◀──────────────| 1 | 1 | 450.00 |
| 2 | Ravi |◀────┐ | 2 | 1 | 120.00 |
+----+------------+ └─────────| 3 | 2 | 990.00 |
+----+-------------+--------+
Rule: the FOREIGN KEY always lives on the MANY side.
One customer has many orders, so customer_id sits on orders.-- One-to-many in SQL
CREATE TABLE customers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
total numeric(10,2) NOT NULL CHECK (total >= 0)
);
-- Index the foreign key: PostgreSQL does NOT create one automatically,
-- and "all orders for this customer" is a query you will run constantly.
CREATE INDEX orders_customer_id_idx ON orders (customer_id);-- ---------- One-to-one ----------
CREATE TABLE user_profiles (
-- The primary key IS the foreign key. That is what makes it one-to-one:
-- each user can have at most one profile row, because the PK must be unique.
user_id bigint PRIMARY KEY REFERENCES customers(id) ON DELETE CASCADE,
bio text,
avatar text
);
-- Use one-to-one when: the extra columns are large, rarely read, or optional.
-- Otherwise just add the columns to the main table — a separate table costs a join.---------- Many-to-many: needs a junction table ----------
orders order_items products
+----+-------+ +----------+------------+ +----+----------+
| id | total | | order_id | product_id | | id | name |
+----+-------+ +----------+------------+ +----+----------+
| 1 |450.00 |◀───────| 1 | 7 |──▶| 7 | Keyboard |
| 2 |120.00 |◀──┐ | 1 | 9 |─┐ | 9 | Mouse |
+----+-------+ └────| 2 | 7 | │ +----+----------+
+----------+------------+ │
└──▶ (product 9)
Order 1 contains two products. Product 7 appears in two orders.
Neither side can hold the link, so a third table holds the PAIRS.-- Many-to-many in SQL
CREATE TABLE products (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
price numeric(10,2) NOT NULL CHECK (price >= 0)
);
CREATE TABLE order_items (
order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id bigint NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
-- Extra facts about the PAIRING belong here, not on either side:
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric(10,2) NOT NULL, -- price AT THE TIME OF SALE
-- A composite primary key: the same product cannot be added twice to one order
PRIMARY KEY (order_id, product_id)
);
-- The composite PK indexes (order_id, product_id). Add the reverse for lookups
-- that start from the product side:
CREATE INDEX order_items_product_id_idx ON order_items (product_id);-- ---------- Users, roles and permissions: many-to-many twice ----------
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE
);
CREATE TABLE roles (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL UNIQUE -- 'admin', 'editor', 'viewer'
);
CREATE TABLE permissions (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL UNIQUE -- 'orders.read', 'orders.write'
);
CREATE TABLE user_roles ( -- junction 1
user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role_id bigint NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
PRIMARY KEY (user_id, role_id)
);
CREATE TABLE role_permissions ( -- junction 2
role_id bigint NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
permission_id bigint NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
PRIMARY KEY (role_id, permission_id)
);-- What can this user actually do? Walk the chain:
SELECT DISTINCT p.name
FROM users u
JOIN user_roles ur ON ur.user_id = u.id
JOIN role_permissions rp ON rp.role_id = ur.role_id
JOIN permissions p ON p.id = rp.permission_id
WHERE u.email = '[email protected]'
ORDER BY p.name;How it works #
The rule for one-to-many never changes: the foreign key lives on the many side. One customer has many orders, so customer_id is a column on orders. Trying to put it the other way round would mean a customer row needing an unknown number of order columns, which is why it cannot work.
One-to-one is expressed by making the foreign key also the primary key. Because a primary key must be unique, each parent can have at most one child row. It is worth pausing before using it, though — if the columns are small and usually needed, putting them on the main table avoids a join for no benefit. One-to-one earns its place when the extra data is large, rarely read, or genuinely optional.
Many-to-many cannot be expressed with a column on either table, because both sides need an unknown number of links. The junction table stores one row per pairing instead. Its primary key is usually the two foreign keys together — a composite primary key — which both identifies the pairing and prevents the same pair being stored twice.
The subtle and important part is that facts about the pairing belong on the junction table. quantity is not a property of the order or of the product; it is a property of "this product, in this order". The same goes for unit_price: storing the price at the time of sale means a later price change does not silently rewrite history in old invoices. That single column prevents a genuinely serious class of bug.
One thing PostgreSQL does not do for you: indexing foreign keys. A primary key gets an index automatically, a foreign key does not. Since "find all orders for this customer" is the query you will run most, that index needs creating by hand. Its absence also makes deleting a parent row slow, because PostgreSQL must scan the child table to check the constraint.
In the junction table, the composite primary key (order_id, product_id) creates an index in that column order, which serves lookups starting from order_id. Lookups starting from product_id need their own index — hence the second one.
Real-world use #
Most design mistakes are a many-to-many relationship that someone tried to force into a single column. The warning signs are a column called tags holding "sql,postgres,database", or columns named role1, role2, role3. Both work until the day you need to count, filter or join on those values, at which point they become very painful to unpick.
The users-roles-permissions shape above is worth internalising, because it appears in nearly every serious application. Permissions attach to roles rather than directly to users, so granting someone access is one row in user_roles instead of twenty.
When a query joins four tables and the shape is unclear, draw the boxes and arrows. Nearly every "why is this query returning duplicate rows" question turns out to be an unnoticed many-to-many fan-out — one order matching three items produces three rows, which is correct but surprising if you expected one.
Decide ON DELETE behaviour per relationship, not as a blanket policy. order_items should cascade: a line item has no meaning without its order. products should restrict: deleting a product that appears in historical orders would destroy records you need for accounting. The two junction tables in the same schema can reasonably have different rules.
Common mistakes #
- Storing a many-to-many relationship as a comma-separated list, which cannot be joined, counted or validated.
- Putting the foreign key on the "one" side instead of the "many" side.
- Forgetting to index foreign key columns, making lookups and parent deletes slow.
- Storing the current product price on the order instead of the price at the time of sale.
- Being surprised by duplicate rows from a join, when it is a normal many-to-many fan-out.
Practice #
Model a simple blog: authors write posts (one shape), posts carry tags where a tag applies to many posts (another shape), and each post has exactly one set of SEO metadata that is large and rarely read (a third shape). Write the CREATE TABLE statements with correct foreign keys, pick a deliberate ON DELETE rule for each, and add the indexes PostgreSQL will not create for you. Then write one query listing every post with a given tag.