From Requirements to ERD

Good tables start before CREATE TABLE. An ERD, short for entity relationship diagram, is the small planning sketch that turns a written requirement into tables, columns, and foreign keys before you type DDL.

Section 9 taught you how to create a schema. Section 10 asks the earlier question: what schema should you create?

Start with the sentence

Imagine a tiny cafe counter app with this requirement:

Customers place pickup orders. Each order belongs to one customer. An order can contain several drink lines. Each drink line stores the drink name and quantity.

Do not open the SQL editor yet. First underline the nouns and verbs.

An entity is a kind of thing your database needs to remember: customer, pickup_order, order_drink. An attribute is one fact about an entity: a customer's email, an order's placed time, a line's quantity. A relationship is how entities connect: a customer places orders; an order contains drink lines.

This is modeling before typing. If you skip it, you tend to make tables that mirror the first screen you saw: one giant orders table with customer email, drink 1, drink 2, drink 3, pickup time, notes, and hope. That shape feels fast for the first insert and expensive for every query after it.

Draw the ERD

Draw boxes for entities, list the attributes inside, then draw lines for relationships:

Customer
  customer_id PK
  email
  display_name
 
PickupOrder
  order_id PK
  customer_id FK -> Customer.customer_id
  order_label
  placed_at
 
OrderDrink
  order_id FK -> PickupOrder.order_id
  line_no
  drink_name
  quantity
  PK (order_id, line_no)
 
Customer 1 ───< PickupOrder 1 ───< OrderDrink

That 1 ───< is the plain-text version of crow's foot notation: the single line means "one," and the forked foot means "many." You will see polished diagrams where the many side looks like a little three-pronged foot. The notation matters less than the decision it records.

Use tools that stay out of the way: a whiteboard, paper, a text note, or any diagrammer that lets you draw boxes and lines without turning the model into a design project. The goal is not artwork. The goal is a shared answer to "what rows exist, and how do they point at each other?"

Cardinality comes from the business rule

Cardinality means how many rows on one side may relate to how many rows on the other side: one-to-one, one-to-many, or many-to-many.

The cafe requirement gives you two one-to-many decisions:

• One customer can place many pickup orders. Each pickup order belongs to exactly one customer.
• One pickup order can contain many drink lines. Each drink line belongs to exactly one order.

Those words decide where the foreign key goes. In a one-to-many relationship, the foreign key lives on the many side: pickup_orders.customer_id, then order_drinks.order_id.

Cardinality is not guessed from today's sample rows. If Ada has only one order in a test spreadsheet, that does not make customer-to-order one-to-one. Ask the real rule: can the cafe accept a second order from the same customer? If yes, model one-to-many. If a drink line can belong to two orders at once, your story is broken; the line is part of one order.

Translate the ERD mechanically

Once the ERD is honest, the DDL is almost mechanical:

1. Entity boxes become tables.
2. Attributes become columns.
3. Primary keys identify rows.
4. One-to-many lines become foreign keys on the many side.
5. The row's grain decides composite keys when a child row only has meaning inside its parent.

Here is the diagram above as live PostgreSQL:

CREATE TABLE counter_customers (
  customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  display_name text NOT NULL
);
 
CREATE TABLE pickup_orders (
  order_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES counter_customers(customer_id),
  order_label text NOT NULL,
  placed_at timestamptz NOT NULL
);
 
CREATE TABLE order_drinks (
  order_id integer NOT NULL REFERENCES pickup_orders(order_id),
  line_no integer NOT NULL,
  drink_name text NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, line_no)
);
 
INSERT INTO counter_customers (email, display_name)
VALUES
  ('ada@cafe.test', 'Ada'),
  ('ken@cafe.test', 'Ken');
 
INSERT INTO pickup_orders (customer_id, order_label, placed_at)
VALUES
  (1, 'morning pickup', TIMESTAMPTZ '2026-06-03 08:05:00+00'),
  (1, 'beans for home', TIMESTAMPTZ '2026-06-03 08:20:00+00'),
  (2, 'tea break', TIMESTAMPTZ '2026-06-03 10:15:00+00');
 
INSERT INTO order_drinks (order_id, line_no, drink_name, quantity)
VALUES
  (1, 1, 'Latte', 2),
  (1, 2, 'Cold Brew', 1),
  (2, 1, 'Whole Bean Bag', 1),
  (3, 1, 'Sencha', 1);
 
SELECT 'counter_customers' AS table_name, count(*) AS row_count
FROM counter_customers
UNION ALL
SELECT 'pickup_orders' AS table_name, count(*) AS row_count
FROM pickup_orders
UNION ALL
SELECT 'order_drinks' AS table_name, count(*) AS row_count
FROM order_drinks
ORDER BY table_name;

CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 3
INSERT 0 4
    table_name     | row_count
-------------------+-----------
 counter_customers |         2
 order_drinks      |         4
 pickup_orders     |         3
(3 rows)

The row-count check is not decoration. It proves the model's grain: customers, orders, and drink lines are three different row types. One customer row did not become three customer rows just because Ada ordered twice; one order row did not need drink_1, drink_2, and drink_3 columns just because an order can have multiple drinks.

You can inspect the table shape as data too:

SELECT table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name IN ('counter_customers', 'pickup_orders', 'order_drinks')
ORDER BY CASE table_name
           WHEN 'counter_customers' THEN 1
           WHEN 'pickup_orders' THEN 2
           WHEN 'order_drinks' THEN 3
         END,
         ordinal_position;

    table_name     | column_name  |        data_type         | is_nullable
-------------------+--------------+--------------------------+-------------
 counter_customers | customer_id  | integer                  | NO
 counter_customers | email        | text                     | NO
 counter_customers | display_name | text                     | NO
 pickup_orders     | order_id     | integer                  | NO
 pickup_orders     | customer_id  | integer                  | NO
 pickup_orders     | order_label  | text                     | NO
 pickup_orders     | placed_at    | timestamp with time zone | NO
 order_drinks      | order_id     | integer                  | NO
 order_drinks      | line_no      | integer                  | NO
 order_drinks      | drink_name   | text                     | NO
 order_drinks      | quantity     | integer                  | NO
(11 rows)

Notice how little imagination the translation required after the model was clear. The diagram said "one customer, many orders," so customer_id went into pickup_orders. It said "one order, many lines," so order_id went into order_drinks. It said a line is identified inside an order, so (order_id, line_no) became the primary key.

Try the translation yourself

The playground starts from an empty local database. Change the requirement in the comments, then change the ERD-to-DDL translation to match it. If you make a relationship point at a missing parent, PostgreSQL will tell you immediately.

An ERD is not a ceremony. It is a cheap way to make the expensive decisions while they are still easy to change. Next, 10.2 turns one of those decisions into a discipline: normalization, the habit of keeping each fact in the place where it belongs.