Database Design
Entity-Relationship Modelling
A schema is the hardest thing in a system to
JrCodex·7 min read
Jr Codex DBMS Notes
Level: Intermediate Prerequisites: Module 3, Chapter 6: Views, Transactions and DML Time to complete: ~20 minutes
Table of Contents
- Designing Before Typing
- Entities and Attributes
- Relationships and Cardinality
- Participation
- Weak Entities
- Reading a Domain into a Model
- Summary & Next Steps
1. Designing Before Typing
Why a Diagram First
─────────────────────────────────────────
A schema is the hardest thing in a system to
change. Application code is rewritten every year;
the tables underneath it outlive several
rewrites, and every migration risks live data.
An ER diagram is a design you can be wrong on
cheaply. Redrawing an arrow costs a minute.
Re-shaping a populated table costs a migration,
a maintenance window, and a rollback plan.
─────────────────────────────────────────
An entity-relationship model describes a domain as things and the associations between them, without yet committing to tables.
2. Entities and Attributes
The Vocabulary
─────────────────────────────────────────
ENTITY a thing the business cares about
Student, Course, Order
Drawn as a RECTANGLE.
ENTITY SET all entities of that type — this
becomes a table.
ATTRIBUTE a property of an entity
name, credits, placed_at
Drawn as an OVAL.
KEY ATTRIBUTE uniquely identifies an entity.
UNDERLINED.
─────────────────────────────────────────
Attribute Varieties
─────────────────────────────────────────
SIMPLE atomic — marks, title
COMPOSITE decomposes — address splits into
street, city, postcode
Decide whether you will ever query
the parts. If yes, store them
separately.
MULTIVALUED many values per entity — a student's
phone numbers.
Cannot be stored in one column
(Module 2, Chapter 1's atomicity).
Becomes its own TABLE.
DERIVED computable from others — age from
date_of_birth.
Usually NOT stored; compute it, or
it goes stale.
─────────────────────────────────────────
The Multivalued Rule
─────────────────────────────────────────
If an attribute can hold more than one value,
it is not an attribute. It is a separate entity
with a relationship.
✗ students.phone_numbers = "9876, 9123"
✓ a phones table referencing students
This is the single most common beginner design
error, and Chapter 4 formalises why it is wrong.
─────────────────────────────────────────
3. Relationships and Cardinality
Cardinality — How Many Relate to How Many
─────────────────────────────────────────
ONE-TO-ONE (1:1)
Each A relates to at most one B, and vice
versa.
A person and their passport.
ONE-TO-MANY (1:N)
One A relates to many B; each B to one A.
A department has many courses; each course
belongs to one department.
── the most common by far
MANY-TO-MANY (M:N)
Many A relate to many B.
Students enrol in many courses; courses have
many students.
─────────────────────────────────────────
The Diagram
─────────────────────────────────────────
┌──────────┐ ◇ ┌──────────┐
│ Student │──── M ──enrols──N ──│ Course │
└──────────┘ └──────────┘
│ │
(name) (title)
(city) (credits)
Rectangles = entities
Diamond = relationship
M, N = cardinality
─────────────────────────────────────────
Relationships Can Have Attributes
─────────────────────────────────────────
A grade belongs to neither the student nor the
course. It belongs to the ENROLMENT — the
relationship itself.
Any attribute that only makes sense for a
specific PAIRING belongs on the relationship,
and it is the clearest sign that you have a
genuine M:N relationship rather than a lookup.
─────────────────────────────────────────
4. Participation
Cardinality says how many. Participation says whether it is optional.
Two Kinds
─────────────────────────────────────────
TOTAL (mandatory)
Every entity MUST participate.
Every order must belong to a customer.
Drawn as a DOUBLE LINE.
──► NOT NULL foreign key
PARTIAL (optional)
An entity MAY participate.
A student may be enrolled in nothing.
Drawn as a SINGLE LINE.
──► nullable foreign key, or simply no row
─────────────────────────────────────────
Why It Matters More Than It Looks
─────────────────────────────────────────
Participation is the difference between
NOT NULL and nullable — and that decision
propagates into every query written against the
table forever.
A nullable foreign key means every join to it
must consider whether to be an INNER or LEFT
join (Module 3, Chapter 3), and every aggregate
must consider NULL (Chapter 4).
Getting participation right at design time
removes a permanent tax on the queries.
─────────────────────────────────────────
5. Weak Entities
An entity that cannot be identified without its parent.
The Example
─────────────────────────────────────────
An ORDER LINE. "Line 3" means nothing on its own
— line 3 OF WHICH ORDER?
Its key is the parent's key plus a local
discriminator:
(order_id, line_number)
Drawn as a DOUBLE RECTANGLE, connected by a
double-diamond IDENTIFYING relationship.
─────────────────────────────────────────
The Test
─────────────────────────────────────────
"If the parent is deleted, does this still mean
anything?"
NO ──► weak entity. Composite key including the
parent, and ON DELETE CASCADE
(Module 2, Chapter 2).
YES ──► an ordinary entity with its own key and
a reference. Use RESTRICT.
Order lines, comment replies and invoice items
are weak. Orders, customers and products are not.
─────────────────────────────────────────
6. Reading a Domain into a Model
A practical technique: extract the model from a plain description of the business.
The Grammar Heuristic
─────────────────────────────────────────
NOUNS ──► candidate ENTITIES
ADJECTIVES ──► candidate ATTRIBUTES
VERBS ──► candidate RELATIONSHIPS
QUANTIFIERS ──► CARDINALITY
("each", "many", "at most one")
A first pass, not a rule. It gets you a draft to
argue with, which is much faster than starting
from nothing.
─────────────────────────────────────────
Worked Through
─────────────────────────────────────────
"A library has many books. Each book has a title
and an ISBN, and may have several copies. A
member can borrow many copies, one at a time,
and we record when each was borrowed and
returned."
ENTITIES Library, Book, Copy, Member
ATTRIBUTES Book: title, ISBN
Copy: barcode, condition
Member: name, joined_on
RELATIONSHIPS Library 1—N Book
Book 1—N Copy
Member M—N Copy (via Borrowing)
ON THE borrowed_on, returned_on
RELATIONSHIP ── they describe the PAIRING, not
the member or the copy
WEAK ENTITY Copy is weak: "copy 3" means
nothing without its Book
─────────────────────────────────────────
The Distinction Worth Noticing
─────────────────────────────────────────
BOOK and COPY are different entities, and
conflating them is the classic modelling error
here.
"The Hobbit" is a book. The battered paperback
with barcode 88213 is a copy. You borrow a COPY,
you search for a BOOK.
Whenever a noun means both an abstract thing and
a physical instance, you have two entities.
─────────────────────────────────────────
7. Summary & Next Steps
Key Takeaways
- A schema outlives the code written against it, so being wrong on a diagram is far cheaper than being wrong in a populated table.
- An attribute that can hold multiple values is not an attribute — it is a separate entity, and storing it as a delimited string is the most common beginner error.
- Attributes that only make sense for a specific pairing belong on the relationship, and are the clearest sign of a genuine many-to-many.
- Participation determines NOT NULL versus nullable, which then taxes every join and aggregate written against that column forever.
Concept Check
- Why does a student's
gradebelong to the enrolment rather than to the student or the course? - Apply the weak-entity test to a comment on a blog post, and to the blog post itself.
- In the library example, why are Book and Copy separate entities rather than one?
Next Chapter
→ Chapter 2: From ER to Relational Schema
Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index