In this chapter
We'll draw GreenMart's three tables on paper — and learn cardinality, the missing piece that shows exactly how Customer, Product, and Sale actually relate.
The Problem in Real Life
Mike wants to hire a part-time cashier for weekends. Before training starts, Mike asks Sarah a fair question: can you draw the whole system on one page, so I can actually see how it fits together, without opening the database at all?
Sarah draws three boxes — Customer, Product, Sale — and stops. Boxes alone don't say how they connect. Can one customer have many sales? Can one sale involve many products? Nothing on the page answers that yet.
| Entity |
|---|
| Customer |
| Product |
| Sale |
Three boxes, correctly named. Nothing here says how any of them actually relate to each other.
A box for each table isn't a diagram — it's just a list.
Sarah
Connected Boxes vs. Cardinality
Connections are invisible
Three boxes on a page don't say how any of them actually relate.
Easy to design wrong
Guessing at a relationship instead of stating it clearly can build the wrong schema entirely.
Hard to explain to someone new
A new hire can't understand the system from raw tables alone.
"How many" questions go unanswered
Can one sale involve many products? Nothing says so without a real diagram.
What Is an ER Diagram, Really?
An ER diagram (entity-relationship diagram) draws a database's tables as boxes and the relationships between them as labeled lines — a picture of the whole schema, readable by someone who's never opened the database at all. GreenMart already has the boxes; every entity from chapters 3 through 7 is already decided. What's missing is labeling exactly how each pair of boxes relates.
That label has a name: cardinality — the specific way two entities connect, stated as how many of one can be linked to how many of the other. Every relationship in a real schema falls into one of three shapes.
Applying that to GreenMart's own three entities settles the question Sarah got stuck on:
- One-to-one — one row in A matches exactly one row in B. Rare in GreenMart's schema so far; nothing here needs it yet.
- One-to-many — one row in A can match many rows in B, but each row in B matches only one row in A. One Customer can have many Sales, but each Sale belongs to exactly one Customer. Same shape between Product and Sale.
- Many-to-many — many rows in A can match many rows in B, and the reverse is also true. Many customers have bought many products, and many products have been bought by many customers — this is the actual relationship between Customer and Product, and it can't be drawn as one direct line between them.
| Entity A | Cardinality | Entity B |
|---|---|---|
| Customer | one → many | Sale |
| Product | one → many | Sale |
| Customer | many ↔ many (through Sale) | Product |
Sale isn't just a third box — it's what actually resolves the many-to-many relationship between Customer and Product. Every Sale row holds one Customer ID and one Product ID, so many customers can connect to many products without either table repeating itself.
GreenMart's Schema — ER Diagram
Customer
Customer ID (PK) · Name · Phone
Sale
Sale ID (PK) · Customer ID (FK) · Product ID (FK) · Date
Product
Product ID (PK) · Name · Price
That's an actual ER diagram: each entity is a box listing its own attributes, with its primary key marked; each relationship is a line between two boxes, labeled with exactly the cardinality decided above. The visual shape matters less than the decision behind it — this same information, decided honestly, is what any real diagram gets drawn from.
The real value was never the drawing itself. It's being forced to answer, on paper, every question a query will eventually ask: how many of this connects to how many of that. Get that wrong here, and every query built on top of it later inherits the same mistake.
Key Takeaway
An ER diagram's real job isn't decoration — it's forcing every relationship's cardinality to be decided honestly, before a single table gets built.
Why This Matters
This chapter is really a review of everything Act 1 decided, seen as one connected picture instead of seven separate lessons. It's also a direct rehearsal for what's ahead: every join in Act 3 works precisely because a relationship's cardinality was modeled correctly here, and the checkpoint immediately after this chapter hands the reader the exact same blank-page problem Sarah just solved — design GreenMart's first real schema from scratch.
Every piece from this Act — entities, keys, links, rules, types, and now the relationships between them — is fully mapped out, on paper, before a single line of SQL exists. The checkpoint next is where the reader does this from scratch.