34. ER modeling: entities, relationships, cardinality

Why model before you write CREATE TABLE

Every lesson so far has used pagila's already-designed schema. This module is about the step that comes before any CREATE TABLE statement exists: deciding what tables should even be, and how they relate. Getting this wrong is expensive to fix later (a schema change on a live table with real data is a carefully planned production migration, not a five-second edit) — so it's worth a deliberate design pass, and Entity-Relationship (ER) modeling is the standard vocabulary for that pass.

Entities: the "nouns" of your domain

An entity is a distinct thing worth tracking — it will typically become a table. In pagila's domain: Customer, Film, Actor, Rental, Payment, Store. The test for "is this really an entity, or just an attribute of one": does it need its own identity, its own attributes, and can it exist (conceptually) somewhat independently? An actor's name is an attribute of the Actor entity; the actor themselves is the entity.

Attributes: the "adjectives"

Each entity has attributes — the columns it'll eventually have. Customer has first_name, last_name, email. One attribute (or a combination) is designated the entity's identifier — this becomes the primary key (recall Module 2's constraints lesson; the schema-design capstone practices choosing these well).

Relationships: the "verbs"

A relationship connects two entities — expressed, once built, as a foreign key (Module 2). Customer rents Film (via Rental). Film has Actor (via Film_Actor). Naming the relationship with a verb, out loud, while designing, is a genuinely useful habit — "a Store employs Staff," "a Staff member processes a Payment" — it forces you to articulate what the connection actually means, not just that two tables happen to share an ID.

Cardinality: how many, on each side

This is the part that determines your actual schema shape, so it's worth being rigorous about it.

• One-to-many (1:N) — one row on the "one" side relates to many rows on the "many" side, but each row on the "many" side relates to only one row on the "one" side. Customer → Rental: one customer has many rentals; each rental belongs to exactly one customer. This is implemented with a foreign key on the "many" side (rental.customer_id).
• Many-to-many (M:N) — rows on either side can relate to multiple rows on the other. Film ↔ Actor: one film has many actors, one actor appears in many films. This cannot be implemented with a single foreign key on either table — it requires a third table (a junction or bridge table), exactly what film_actor is: two foreign keys, one row per actual (film, actor) pairing.
• One-to-one (1:1) — rarer; one row on each side relates to exactly one row on the other. Often used to split a table for access-control or performance reasons (operational concerns beyond this course's scope) rather than being a "natural" real-world relationship.

Getting cardinality wrong at design time is the single most common root cause of the fan-out bugs from Module 4 — a join written assuming 1:N when the real relationship is M:N will multiply rows in exactly the way that lesson diagnosed.

Drawing it: the ER diagram

The standard visual notation — entities as boxes, relationships as lines between them, cardinality marked at each end (commonly "crow's foot" notation: a single line end means "one," a forked/crow's-foot end means "many"). GUI database tools can draw this for you — DBeaver's View Diagram feature, for instance, generates exactly this from a real schema's actual foreign keys. The skill this lesson is building is the reverse direction — going from a business requirement to a diagram before any table exists, which DBeaver obviously can't do for you.

A minimal worked example — modeling a new feature for pagila, film reviews, from scratch:

Customer (1) ----writes---- (N) Review (N) ----about---- (1) Film

Read as: one customer writes many reviews; each review is written by exactly one customer and is about exactly one film; one film can have many reviews. This single line of notation directly tells you Review needs two foreign keys (customer_id, film_id) and is itself the "many" side of two separate 1:N relationships — which is exactly the DDL the next lessons will have you write.

Check yourself

1. What test distinguishes "this should be its own entity" from "this is just an attribute of an existing entity"?
2. Why does a many-to-many relationship require a third (junction) table, when a one-to-many relationship doesn't?
3. Model, in the same crow's-foot-style notation as the Review example: a Store employs Staff, where each staff member works at exactly one store, and a store can have several staff members. Which table gets the foreign key?