Lesson 09 · Design

Entity-relationship modelling

Before you write CREATE TABLE, draw the world you want to store. An entity-relationship (ER) model names the things (entities), the facts about them (attributes, one of them a key) and how they connect (relationships, each one-to-many or many-to-many). Then fixed rules turn the diagram into tables: follow a small library from a one-line brief to four PostgreSQL tables that refuse bad data and answer who borrowed what.

14 min10 steps5 questionsIntermediate

Fig 9 · Interactive

Loading figure…

or ← →

Check yourself

5 quick questions

Pick an answer to see why. Your score saves with your progress.

01A brief says: "Customers place orders. Each order is placed by one customer." Which are the entities?

Entities are the kinds of things you keep facts about, usually the nouns: customer and order. The verb, place, is the relationship between them.

02One customer places many orders, and each order has exactly one customer. How do you store this relationship?

It is one-to-many, so the key goes on the many side. Each order has one customer, so one customer_id value fits. On the customer side it would not fit: one customer has many orders.

03Students take many courses and each course has many students. What does the mapping rule give you?

A many-to-many relationship needs a table of its own. Each row pairs one student with one course, and it can hold the relationship's own facts, such as the grade.

04In the figure's library, someone runs INSERT INTO loan VALUES (2, 99, '2026-05-01'); What does PostgreSQL do?

loan.book_id REFERENCES book (id), so every value must match a book row. PostgreSQL answers: insert or update on table "loan" violates foreign key constraint "loan_book_id_fkey".

05Where does the date of a loan belong?

loaned_on is an attribute of the borrows relationship. A book is lent many times and a member borrows many times, so only the loan row has exactly one date.

Key terms

Glossary

Entity: A kind of thing you keep facts about, such as book or member. It becomes a table.
Attribute: One fact about an entity, such as a book's title. It becomes a column.
Key: An attribute (or set of attributes) that picks out exactly one thing. It becomes the table's PRIMARY KEY.
Relationship: A link between entities, named with a verb: an author writes a book. Drawn as a diamond in Chen's notation.
Cardinality: How many things on each side of a relationship: one-to-one (1:1), one-to-many (1:N) or many-to-many (M:N).
Foreign key: A column whose values must match the key of a row in another table, such as book.author_id → author.id.
Junction table: A table made from a many-to-many relationship. Each row pairs one key from each side, such as loan(member_id, book_id, loaned_on).
ER diagram: A drawing of entities, attributes and relationships, made before writing any CREATE TABLE.

Fine print

Notes and sources

Real books can have several authors. Then writes would be many-to-many too, and it would get its own table (book_author) just like loan. The figure keeps one author per book so you can see both kinds.
This figure uses Chen’s original notation: boxes, diamonds, and 1, N and M on the lines. Many tools draw “crow’s foot” lines instead; the ideas are the same.
A foreign key does not create an index on its own column in PostgreSQL. Joins and deletes that look up book.author_id or loan.book_id are faster if you add one (CREATE INDEX ON loan (book_id)). The primary key already gives loan an index that starts with member_id.
SQLite understands the same CREATE TABLE statements, but it only checks foreign keys after PRAGMA foreign_keys = ON on each connection.
The join uses ORDER BY loan.loaned_on so the rows come back in date order; without ORDER BY, SQL promises no order.
The ids here are surrogate keys: numbers made up by the database designer. A natural key such as an ISBN can also work, if it is truly unique and never changes.

Quiz Unlocks after the last step.