Designing Tables and Relationships

Good database design starts by identifying the things (entities) your application deals with, and how they relate.

One-to-many

The most common relationship: one customer has many orders; each order belongs to one customer. The "many" side holds a foreign key:

customers (id, name, ...)  1 ────< many  orders (id, customer_id, ...)

Many-to-many

An order contains many products, and a product appears in many orders. A many-to-many relationship needs a junction table (also called a join or link table) with a foreign key to each side:

orders 1 ──< order_items >── 1 products

That's exactly our order_items table. Its primary key is the pair (order_id, product_id), and it can also hold facts about the relationship itself — the quantity, and the price paid at the time.

SELECT o.id AS order_id, COUNT(*) AS products, SUM(oi.quantity) AS units
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id
ORDER BY o.id;

+----------+----------+-------+
| order_id | products | units |
+----------+----------+-------+
|        1 |        2 |     3 |
|        2 |        1 |     1 |
|        3 |        2 |     2 |
|        4 |        2 |     4 |
|        5 |        1 |     1 |
|        6 |        1 |     1 |
|        7 |        2 |     2 |
|        8 |        1 |     3 |
+----------+----------+-------+

One-to-one

Each row relates to at most one row in another table — for example, a users table and a user_profiles table with optional details. It's implemented as a foreign key with a UNIQUE constraint (or by sharing the primary key). Often, one table is simpler.

Design guidelines

• One table per entity; each column describes that entity.
• Every table has a primary key. A surrogate key (an auto-generated id) is the usual choice; natural keys (like email) can change.
• Store each fact once, and refer to it by key elsewhere.
• Use the right types and constraints — dates as dates, required values NOT NULL, references as foreign keys.
• Store history when it matters. order_items.unit_price records the price paid, which stays correct even when products.price changes later.
• Name consistently: customer_id for a foreign key to customers.

ER diagrams

An entity-relationship diagram draws tables as boxes and relationships as lines. Sketching one before writing CREATE TABLE statements catches design problems early; tools such as dbdiagram.io, DBeaver, and pgAdmin can draw them from your schema.