Entity Relationship Diagram

Why draw the database before you build it?

Picture opening a spreadsheet nobody labeled: columns with mystery headers, duplicate rows, and no obvious way to tell which sheet “owns” which fact. Relational design starts earlier—with a shared map everyone can argue with using the same vocabulary.

An Entity Relationship (ER) diagram is that map. It shows what you store (entities), what you record about each thing (attributes), and how things connect (relationships). Teams use ER diagrams to align on structure, catch mistakes cheaply, and document the system long after the first developer moves on. You will also see them called ERDs or ER models; they borrow a grammar-like habit—entities as nouns, relationships as verbs.

Why bother?

• Shared picture — Technical and non-technical stakeholders can point at the same boxes and lines instead of guessing from table names alone.
• Blueprint — You decide structure before CREATE TABLE commits you to awkward fixes.
• Living documentation — Future you (and new teammates) see intent, not only the final SQL.
• Fewer surprises — Missing relationships and impossible cardinalities show up on paper first.

The building blocks

Entity

A definable thing you care to store data about: a person, object, concept, or event. Treat entities as nouns—Customer, Student, Product, Shipment. On a diagram they usually appear as rectangles.

Attribute

Attributes are the facts you store about an entity: names, dates, counts, codes. They answer “what do we know about each instance?”

Types of attributes:

• Simple — Not meaningfully split further (Age, SKU).
• Composite — Naturally splits into parts (Address → street, city, postal code).
• Derived — Computed from other data (Age from birth date); often omitted from storage or stored for convenience.
• Multi-valued — Several values per entity (multiple phone numbers); in physical design this often becomes its own table.
• Key — Uniquely identifies an instance (Student ID, order number); underlined or marked PK on many diagrams.

Relationship

A relationship is how entities link or act on each other—enrolls in, places, assigned_to. Read it as a verb between two nouns: Student enrolls in Course. Visually you may see a diamond (Chen style) or a labeled line (common in Crow’s Foot tooling).

Notation families

Several styles exist; each trades readability for tradition in a given tool or textbook.

This lesson focuses on Crow’s Foot (Information Engineering) notation because it appears everywhere in industry tools and job-facing schema sketches.

Crow’s Foot notation

The crow’s foot is the forked symbol that means “many.” Together with a straight bar (one) and a small circle (zero, optional), you can read maximum counts and whether a link is required without decoding a separate legend every time.

How entities are drawn

• Rectangle with the entity name (often with attributes listed inside).
• Primary keys underlined or tagged PK.
• Foreign keys tagged FK when the diagram is close to implementation.

Line endings (the quick decoder)

Cardinality and optionality

Cardinality — At most, how many partners can this side have: one or many?

Optionality — Must the link exist? Mandatory reads as a bar (|); optional reads as a circle (○).

Frequent combinations:

Relationship shapes you will meet

One-to-one (1:1)

Each row on side A pairs with exactly one row on side B, and vice versa.

Example: One person, one passport number record you treat as a separate entity.

┌──────────┐          ┌──────────┐
│  Person  │──|────|──│ Passport │
└──────────┘          └──────────┘

More examples: employee ↔ parking permit; user ↔ profile row (when split for security or size).

One-to-many (1:N)

One parent can have many children; each child points back to one parent.

Example: One customer, many orders—each order belongs to one customer.

┌──────────┐          ┌──────────┐
│ Customer │──|────<──│  Order   │
└──────────┘          └──────────┘

More examples: department → employees; author → books (single-author model).

Many-to-many (M:N)

Rows on both sides can link to multiple partners—classic enrollments, tags, team membership.

Example: Students take many courses; each course has many students.

┌──────────┐          ┌──────────┐
│ Student  │──<────>──│  Course  │
└──────────┘          └──────────┘

Reality check for SQL: You do not implement M:N as two foreign keys on one table in the obvious way. You introduce a junction (bridge / associative) entity so the database only has 1:N edges.

Resolving many-to-many in a relational model

Conceptual sketch:

Student ──<────>── Course

Physical pattern: two foreign keys in the middle.

┌──────────┐          ┌────────────┐          ┌──────────┐
│ Student  │──|────<──│ Enrollment │──>────|──│  Course  │
└──────────┘          └────────────┘          └──────────┘

The junction (Enrollment) usually holds Student_ID, Course_ID, and facts about the pairing (enrolled_on, grade).

How to read a line out loud

Use a simple template:

“[Entity A] [verb] [cardinality phrase] [Entity B].”

Then flip the sentence and read toward Entity A.

Customer–Order (crow’s-foot pattern |──< from Customer toward Order):

• “One customer places one or many orders.”
• “Each order is placed by exactly one customer.”

Student–Course (optional-many both ways, often drawn >──<):

• “One student enrolls in zero or many courses.”
• “One course has zero or many students.”

If both readings sound wrong for the business rule, the diagram—not the database—needs fixing.

Key notes

What’s next

Crow’s Foot diagrams are the sketchpad for relational design: entities in boxes, attributes as fields, lines carrying cardinality and optionality. When you open the next lessons, you will translate these shapes into keys, tables, and constraints—and the diagram will still be the fastest way to explain your schema to another human.