From ER diagram to tables

Part of the free LogicWiz SQL & Databases course, Module 9: ER modelling. The queries and practice run in the app on real Postgres.

Aarav and Meher have left college and are building a food delivery startup. The whiteboard is full of boxes and diamonds. How do they turn it into CREATE TABLEs without missing anything?

You'll learn to

• Apply the mapping rules for every ER piece
• Place relationship attributes in the right table
• Map recursive and ternary relationships
• Write the schema for a full design

Learn

The mapping rules

The food app: entities

The food app: relationships

The design, as SQL

CREATE TABLE customers (
  customer_id  INT PRIMARY KEY,
  name         VARCHAR(40) NOT NULL,
  city         VARCHAR(30) NOT NULL,
  referred_by  INT REFERENCES customers(customer_id)
);
CREATE TABLE restaurants (
  restaurant_id  INT PRIMARY KEY,
  name           VARCHAR(40) NOT NULL,
  city           VARCHAR(30) NOT NULL
);
CREATE TABLE menu_items (
  item_id        INT PRIMARY KEY,
  restaurant_id  INT NOT NULL REFERENCES restaurants(restaurant_id),
  name           VARCHAR(40) NOT NULL,
  price          NUMERIC(7,2) NOT NULL
);
CREATE TABLE partners (
  partner_id  INT PRIMARY KEY,
  name        VARCHAR(40) NOT NULL
);
CREATE TABLE orders (
  order_id       INT PRIMARY KEY,
  customer_id    INT NOT NULL REFERENCES customers(customer_id),
  restaurant_id  INT NOT NULL REFERENCES restaurants(restaurant_id),
  partner_id     INT REFERENCES partners(partner_id),
  ordered_at     TIMESTAMP NOT NULL
);
CREATE TABLE order_items (
  order_id  INT REFERENCES orders(order_id),
  item_id   INT REFERENCES menu_items(item_id),
  qty       INT NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, item_id)
);

SELECT table_name, COUNT(*) AS columns
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_name
ORDER BY table_name;

Five entities and one M:N relationship give six tables. The four 1:N relationships and the recursive "refers" cost no extra table: each is just a foreign key column on the many side.

Where does a relationship attribute go?

qty is not a fact about the order alone (an order has many items) or about the item alone (an item is in many orders). It describes one pair, so it lives in the M:N junction table order_items.

For a 1:N relationship, its attributes move into the N-side table along with the FK. For example, "partner delivers order" with delivered_at puts delivered_at into orders.

A quick test: ask "this value belongs to one what?" and put it in that table.

Walking the M:N relationship

SELECT o.order_id, m.name AS item, oi.qty, m.price * oi.qty AS amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN menu_items m ON m.item_id = oi.item_id
WHERE o.customer_id = 1
ORDER BY o.order_id, item;

Customer → orders (1:N) → order_items (the junction) → menu_items. Every hop follows a foreign key from the design table.

A recursive relationship is a self-join

SELECT c.name AS customer, r.name AS referred_by
FROM customers c
JOIN customers r ON r.customer_id = c.referred_by
ORDER BY c.customer_id;

"Customer refers customer" links an entity set to itself. It maps to an FK that points at its own table, and you read it by joining the table to itself under two aliases.

The most common design slip: putting item_id and qty straight into orders. Now an order can hold only one item, and a second item needs a second order row with the customer and time repeated. Whenever you see "an order has many items" and "an item is in many orders", reach for a junction table.

A ternary relationship links three entities at once: a Supplier supplies a Part to a Project, with attribute qty. It maps to one table supplies(supplier_id, part_id, project_id, qty) with an FK to each. If it is M:N:P, all three FKs together are the key. Exams also ask you to count tables for a whole diagram: entities + M:N relationships + multivalued attributes + n-ary relationships (1:N and 1:1 add none).

In one line

Entities → tables, 1:N → FK on the many side, 1:1 → FK + UNIQUE, M:N and ternary → their own table, multivalued → its own table, total participation → NOT NULL.

Interview corner

Walk me through converting an ER diagram into tables.

Each strong entity becomes a table with its key. Weak entities get owner key + partial key. 1:N becomes an FK on the many side, 1:1 an FK with UNIQUE, and M:N a junction table keyed by both FKs. Multivalued attributes get their own table, composite ones are split into columns, and total participation becomes NOT NULL.

Design the tables for a food delivery app.

customers, restaurants, menu_items(restaurant_id FK), partners, orders(customer_id, restaurant_id, partner_id FKs, status) and the junction order_items(order_id, item_id, qty) keyed by the pair. A self-FK customers.referred_by handles referrals. Mention that order_items often also stores the price at order time, because menu prices change.

What is a recursive relationship and how is it stored?

An entity related to itself, like an employee managed by another employee or a customer referred by another customer. It is a foreign key pointing at the same table (manager_id REFERENCES employees(emp_id)), read with a self-join or a recursive CTE.