Data modeling & the ER mindset

Turn a real-world problem into tables and relationships.

From the world to a schema

Before writing SQL, model the problem. Entity–Relationship (ER) modeling names:

Entities — the nouns you store (author, book, customer, order). Each becomes a table.
Attributes — what you know about each (a book's title, price). Each becomes a column.
Relationships — how entities connect, with a cardinality:
- one-to-many (1:N) — one author writes many books → put the FK on the many side (books.author_id).
- many-to-many (M:N) — orders contain many books and books appear in many orders → resolve with a junction table (order_items).
- one-to-one (1:1) — rare; usually a column or an optional side table.

The normal forms (1NF→3NF→BCNF) are just a checklist that a model has no redundant or anomaly-prone storage: every non-key fact depends on the key, the whole key, and nothing but the key. A functional dependency A→B means A determines B; BCNF says every such determinant must be a candidate key.

Sometimes you deliberately denormalize (store a redundant copy) for read speed — but only with eyes open, because you take on the job of keeping the copies in sync.

One author, many books — so the key goes on the many side. Put it the other way round and books would need a list of author ids in one column, which is the 1NF violation you just met.

A junction table is the only way to store M:N without repeating groups. Its primary key is the pair (order_id, book_id) — solid underline — and each half is also a foreign key, drawn dashed as well. Columns like quantity and unit_price belong here because they describe the pairing, not either side.