> ## Documentation Index > Fetch the complete documentation index at: https://miliracle.online/llms.txt > Use this file to discover all available pages before exploring further. # Drawing Entity Relationship Diagrams > Learn to read crow's-foot notation and turn a plain-English brief into an ERD, then into tables and SQL, step by step. export const QUIZ = [{ q: "On the Member–Loan line, a crow's foot sits next to Loan. What does it tell you?", a: ["Each member can have many loans", "Each loan can have many members", "Loan is the primary key"], c: 0, why: "Read across the line. The symbol next to Loan answers “how many loans per member?”." }, { q: "A circle and a bar (o|) sit next to LibraryCard. What do they mean?", a: ["Each member has zero or one card", "Each member has exactly one card", "Each member has one or more cards"], c: 0, why: "The outer symbol is the minimum (circle = zero), the inner one the maximum (bar = one)." }, { q: "Authors write many books, and books have many authors. How do you store that?", a: ["Put authorID in Book", "Put bookID in Author", "Add a bridge table, BookAuthor"], c: 2, why: "One column holds one value. A bridge table has one row per pair and turns the many-to-many into two one-to-many lines." }, { q: "Member ||—o< Loan. Which table gets the foreign key?", a: ["Member", "Loan", "Both"], c: 1, why: "The foreign key goes on the crow's-foot side. Each loan stores one memberID." }, { q: "The brief says “each member has a name and an email”. What is email?", a: ["An entity", "An attribute of Member", "A relationship"], c: 1, why: "It describes a member and has no facts of its own. It would become an entity only if a member could have many." }]; export const PRACTICE_MERMAID = `erDiagram DOCTOR ||--o{ APPOINTMENT : has PATIENT ||--o{ APPOINTMENT : books DOCTOR { int doctorID PK string name } PATIENT { int patientID PK string name } APPOINTMENT { int appointmentID PK int doctorID FK int patientID FK datetime startsAt }`; export const PRACTICE_MODEL = { bw: 140, ents: [{ id: "d", name: "Doctor", x: 5, y: 20, attrs: [["doctorID", "PK"], ["name", ""]], rows: 4 }, { id: "p", name: "Appointment", x: 210, y: 20, attrs: [["appointmentID", "PK"], ["doctorID", "FK"], ["patientID", "FK"], ["startsAt", ""]] }, { id: "t", name: "Patient", x: 415, y: 20, attrs: [["patientID", "PK"], ["name", ""]], rows: 4 }], links: [{ id: "dp", a: "d", as: "r", b: "p", bs: "l", ea: "one", eb: "zeroMany", label: "has" }, { id: "pt", a: "p", as: "r", b: "t", bs: "l", ea: "zeroMany", eb: "one", label: "booked by" }] }; export const PRACTICE = [{ id: "ents", q: "1. Which nouns are entities? Pick all that apply.", multi: true, a: ["Clinic", "Doctor", "Patient", "Appointment", "Date", "Time"], c: [1, 2, 3], why: "Doctor, Patient and Appointment have many instances and facts of their own. There is one clinic, the whole system. Date and time describe an appointment, so they are attributes (startsAt)." }, { id: "far", q: "2. On the Doctor–Appointment line, which symbol goes next to Appointment?", glyph: true, a: ["one", "zeroOne", "oneMany", "zeroMany"], c: [3], why: "It answers “how many appointments per doctor?”. A new doctor has none, a busy one many: zero or more." }, { id: "near", q: "3. On the same line, which symbol goes next to Doctor?", glyph: true, a: ["one", "zeroOne", "oneMany", "zeroMany"], c: [0], why: "It answers “how many doctors per appointment?”. Exactly one." }, { id: "fk", q: "4. Where does the doctorID foreign key go?", a: ["Put it in Doctor", "Put it in Appointment", "Put it in a bridge table"], c: [1], why: "One doctor, many appointments: the key goes on the crow's-foot side. Each appointment stores its doctorID, and its patientID for the same reason." }]; export const CLINIC_BRIEF = "A small clinic has doctors and patients. Each doctor and each patient has a name. A patient books appointments. Each appointment is with exactly one doctor and exactly one patient, at a set date and time. A new doctor may have no appointments yet; a busy one has many. A patient can have zero or more appointments."; export const MistakeCard = ({m}) => { const [fixed, setFixed] = useState(false); const svg = (model, keys, cls, on, label) =>
keys.includes(key) ? cls : "") }} />
; return

{m.title}

{fixed ? "Fixed" : "Mistake"}
{svg(m.bad, m.badKeys, "is-bad", !fixed, `Mistake: ${m.problem}`)} {svg(m.fix, m.fixKeys, "is-hi", fixed, `Fix: ${m.cure}`)}

{m.problem}

{m.cure}

; }; export const MISTAKES = [{ title: "An attribute drawn as an entity", bad: { bw: 120, ents: [{ id: "m", name: "Member", x: 10, y: 14, attrs: [["memberID", "PK"], ["name", ""]], rows: 3 }, { id: "e", name: "Email", x: 350, y: 14, attrs: [["email", ""]], rows: 3 }], links: [{ id: "r", a: "m", as: "r", b: "e", bs: "l", ea: "one", eb: "one", label: "has" }] }, badKeys: ["e:e", "l:r", "c:r"], fix: { bw: 120, ents: [{ id: "m", name: "Member", x: 10, y: 14, attrs: [["memberID", "PK"], ["name", ""], ["email", ""]] }], links: [] }, fixKeys: ["f:m.email"], problem: "Email has no facts of its own. It only describes a member.", cure: "Make it an attribute of Member. Exception: if a member can have many emails, Email is an entity after all, with a one-to-many line." }, { title: "Many-to-many with no bridge", bad: { bw: 120, ents: [{ id: "b", name: "Book", x: 10, y: 14, attrs: [["bookID", "PK"], ["title", ""], ["authorID", "FK"]] }, { id: "a", name: "Author", x: 350, y: 14, attrs: [["authorID", "PK"], ["name", ""]], rows: 3 }], links: [{ id: "r", a: "b", as: "r", b: "a", bs: "l", ea: "oneMany", eb: "oneMany" }] }, badKeys: ["f:b.authorID", "l:r", "c:r"], fix: { bw: 120, ents: [{ id: "b", name: "Book", x: 10, y: 14, attrs: [["bookID", "PK"], ["title", ""]], rows: 3 }, { id: "x", name: "BookAuthor", x: 180, y: 14, attrs: [["bookID", "PK, FK"], ["authorID", "PK, FK"]], rows: 3 }, { id: "a", name: "Author", x: 350, y: 14, attrs: [["authorID", "PK"], ["name", ""]], rows: 3 }], links: [{ id: "bx", a: "b", as: "r", b: "x", bs: "l", ea: "one", eb: "oneMany" }, { id: "xa", a: "x", as: "r", b: "a", bs: "l", ea: "oneMany", eb: "one" }] }, fixKeys: ["e:x"], problem: "Crow's feet on both ends, but Book has room for one authorID.", cure: "Add a bridge table, BookAuthor, with one row per book–author pair." }, { title: "Foreign key on the “one” side", bad: { bw: 120, ents: [{ id: "m", name: "Member", x: 10, y: 14, attrs: [["memberID", "PK"], ["name", ""], ["loanID", "FK"]] }, { id: "l", name: "Loan", x: 350, y: 14, attrs: [["loanID", "PK"], ["dueOn", ""]], rows: 3 }], links: [{ id: "r", a: "m", as: "r", b: "l", bs: "l", ea: "one", eb: "zeroMany" }] }, badKeys: ["f:m.loanID"], fix: { bw: 120, ents: [{ id: "m", name: "Member", x: 10, y: 14, attrs: [["memberID", "PK"], ["name", ""]], rows: 3 }, { id: "l", name: "Loan", x: 350, y: 14, attrs: [["loanID", "PK"], ["memberID", "FK"], ["dueOn", ""]] }], links: [{ id: "r", a: "m", as: "r", b: "l", bs: "l", ea: "one", eb: "zeroMany" }] }, fixKeys: ["f:l.memberID"], problem: "A member row has one loanID cell, but a member can have many loans.", cure: "Move the foreign key to the crow's-foot side: each loan stores its memberID." }, { title: "Symbols at the wrong end", bad: { bw: 120, ents: [{ id: "b", name: "Book", x: 10, y: 14, attrs: [["bookID", "PK"], ["title", ""]], rows: 3 }, { id: "l", name: "Loan", x: 350, y: 14, attrs: [["loanID", "PK"], ["bookID", "FK"]], rows: 3 }], links: [{ id: "r", a: "b", as: "r", b: "l", bs: "l", ea: "zeroMany", eb: "one" }] }, badKeys: ["c:r"], fix: { bw: 120, ents: [{ id: "b", name: "Book", x: 10, y: 14, attrs: [["bookID", "PK"], ["title", ""]], rows: 3 }, { id: "l", name: "Loan", x: 350, y: 14, attrs: [["loanID", "PK"], ["bookID", "FK"]], rows: 3 }], links: [{ id: "r", a: "b", as: "r", b: "l", bs: "l", ea: "one", eb: "zeroMany" }] }, fixKeys: ["c:r"], problem: "This drawing says each book is loaned exactly once, and one loan can cover many books. The brief says the opposite.", cure: "The symbol next to Loan answers “how many loans per book?”. Many, so the crow's foot goes next to Loan." }]; export const sqlLine = ({text}) => text.split(new RegExp(SQL_KW.source, "g")).map((part, i) => i % 2 ? {part} : part); export const SQL_KW = /\b(CREATE|TABLE|INT|DATE|PRIMARY|KEY|NOT|NULL|UNIQUE|REFERENCES|VARCHAR)\b/; export const FK_CASES = [{ tab: "One-to-many", model: { bw: 140, ents: [{ id: "m", name: "Member", x: 5, y: 16, attrs: [["memberID", "PK"], ["name", ""]], rows: 3 }, { id: "l", name: "Loan", x: 415, y: 16, attrs: [["loanID", "PK"], ["memberID", "FK"], ["dueOn", ""]] }], links: [{ id: "r", a: "m", as: "r", b: "l", bs: "l", ea: "one", eb: "zeroMany", label: "makes" }] }, hi: ["f:l.memberID"], rule: "Put the foreign key on the crow's-foot side. Each loan stores its one member. The inner bar next to Member says every loan needs a member, so the column is NOT NULL.", sql: "CREATE TABLE Loan (\n loanID INT PRIMARY KEY,\n memberID INT NOT NULL REFERENCES Member(memberID),\n dueOn DATE NOT NULL\n);" }, { tab: "One-to-one", model: { bw: 140, ents: [{ id: "m", name: "Member", x: 5, y: 16, attrs: [["memberID", "PK"], ["name", ""]], rows: 3 }, { id: "c", name: "LibraryCard", x: 415, y: 16, attrs: [["cardID", "PK"], ["memberID", "FK"], ["issuedOn", ""]] }], links: [{ id: "r", a: "m", as: "r", b: "c", bs: "l", ea: "one", eb: "zeroOne", label: "holds" }] }, hi: ["f:c.memberID"], rule: "Put the foreign key on the optional side. A member may have no card yet, but every card has a member. UNIQUE stops a second card for the same member.", sql: "CREATE TABLE LibraryCard (\n cardID INT PRIMARY KEY,\n memberID INT NOT NULL UNIQUE REFERENCES Member(memberID),\n issuedOn DATE NOT NULL\n);" }, { tab: "Many-to-many", model: { bw: 140, ents: [{ id: "b", name: "Book", x: 5, y: 16, attrs: [["bookID", "PK"], ["title", ""]], rows: 3 }, { id: "x", name: "BookAuthor", x: 210, y: 16, attrs: [["bookID", "PK, FK"], ["authorID", "PK, FK"]], rows: 3 }, { id: "a", name: "Author", x: 415, y: 16, attrs: [["authorID", "PK"], ["name", ""]], rows: 3 }], links: [{ id: "bx", a: "b", as: "r", b: "x", bs: "l", ea: "one", eb: "oneMany", label: "has" }, { id: "xa", a: "x", as: "r", b: "a", bs: "l", ea: "oneMany", eb: "one", label: "names" }] }, hi: ["f:x.bookID", "f:x.authorID"], rule: "Neither side can hold the foreign key. A bridge table holds both, and the pair is its primary key, so the same author can't be listed twice for one book.", sql: "CREATE TABLE BookAuthor (\n bookID INT NOT NULL REFERENCES Book(bookID),\n authorID INT NOT NULL REFERENCES Author(authorID),\n PRIMARY KEY (bookID, authorID)\n);" }]; export const BRIDGE_TABLES = { naive: [{ name: "Book", cols: ["bookID", "title", "authorID"], key: [0], fk: [2], rows: [[1, "Good Omens", 1], [1, "Good Omens", 2], [2, "Mort", 1]], ghost: 1 }, { name: "Author", cols: ["authorID", "name"], key: [0], rows: [[1, "Terry Pratchett"], [2, "Neil Gaiman"]] }], bridge: [{ name: "Book", cols: ["bookID", "title"], key: [0], rows: [[1, "Good Omens"], [2, "Mort"]] }, { name: "BookAuthor", cols: ["bookID", "authorID"], key: [0, 1], fk: [0, 1], rows: [[1, 1], [1, 2], [2, 1]] }, { name: "Author", cols: ["authorID", "name"], key: [0], rows: [[1, "Terry Pratchett"], [2, "Neil Gaiman"]] }] }; export const BRIDGE_MODEL = { bw: 140, ents: [{ id: "b", name: "Book", x: 5, y: 20, attrs: [["bookID", "PK"], ["title", ""], ["authorID", "FK"]] }, { id: "x", name: "BookAuthor", x: 210, y: 20, attrs: [["bookID", "PK, FK"], ["authorID", "PK, FK"]], rows: 3 }, { id: "a", name: "Author", x: 415, y: 20, attrs: [["authorID", "PK"], ["name", ""]], rows: 3 }], links: [{ id: "ba", a: "b", as: "r", b: "a", bs: "l", ea: "oneMany", eb: "oneMany", label: "written by" }, { id: "bx", a: "b", as: "r", b: "x", bs: "l", ea: "one", eb: "oneMany", label: "has" }, { id: "xa", a: "x", as: "r", b: "a", bs: "l", ea: "oneMany", eb: "one", label: "names" }] }; export const RECIPE_STEPS = [{ title: "Find the nouns", mark: "n", show: [], note: "Mark every noun in the brief. Each one is a candidate: library, book, member, name, email, title, ISBN, author, loan, date borrowed, due date." }, { title: "Pick the entities", mark: "e", strike: "x", show: ["e"], note: "Keep the nouns that have many instances and facts of their own: Member, Book, Author, Loan. There is only one library, and it is the whole system, so it is not an entity. Loan began as a verb (borrows), but it has facts of its own, two dates, so it becomes an entity." }, { title: "Add attributes and keys", mark: "a", show: ["e", "a"], note: "The other nouns describe an entity, so they become its attributes. Then give every entity a primary key (PK): an ID that never changes." }, { title: "Draw the relationships", mark: "v", show: ["e", "a", "l"], note: "Each verb that links two entities becomes a line labelled with the verb. A member makes loans, a loan is for a book, a book is written by authors." }, { title: "Set the cardinality", mark: "q", show: ["e", "a", "l", "c"], note: "For every line, ask “how many?” from both sides, and draw each answer next to the box it counts. Book and Author have many on both ends. The next section shows how to store that." }]; export const RECIPE_MODEL = { bw: 140, ents: [{ id: "m", name: "Member", x: 5, y: 24, attrs: [["memberID", "PK"], ["name", ""], ["email", ""]] }, { id: "l", name: "Loan", x: 210, y: 24, attrs: [["loanID", "PK"], ["borrowedOn", ""], ["dueOn", ""]] }, { id: "b", name: "Book", x: 415, y: 24, attrs: [["bookID", "PK"], ["title", ""], ["isbn", ""]] }, { id: "a", name: "Author", x: 415, y: 206, attrs: [["authorID", "PK"], ["name", ""]] }], links: [{ id: "ml", a: "m", as: "r", b: "l", bs: "l", ea: "one", eb: "zeroMany", label: "makes" }, { id: "lb", a: "l", as: "r", b: "b", bs: "l", ea: "zeroMany", eb: "one", label: "is for" }, { id: "ba", a: "b", as: "b", b: "a", bs: "t", ea: "oneMany", eb: "oneMany", label: "written by" }] }; export const BRIEF = [["A ", ""], ["library", "n x"], [" lends ", ""], ["books", "n e"], [" to ", ""], ["members", "n e"], [". Each member has a ", ""], ["name", "n a"], [" and an ", ""], ["email", "n a"], [". Each book has a ", ""], ["title", "n a"], [" and an ", ""], ["ISBN", "n a"], [". A book is ", ""], ["written by", "v"], [" ", ""], ["one or more", "q"], [" ", ""], ["authors", "n e"], [", and an author ", ""], ["writes", "v"], [" ", ""], ["one or more", "q"], [" books. Each author has a ", ""], ["name", "n a"], [". When a member ", ""], ["borrows", "v"], [" a book, the library records a ", ""], ["loan", "n e"], [" with the ", ""], ["date borrowed", "n a"], [" and the ", ""], ["due date", "n a"], [". A member can have ", ""], ["zero or more", "q"], [" loans. Each loan is for ", ""], ["exactly one", "q"], [" book and ", ""], ["exactly one", "q"], [" member. A book can be loaned ", ""], ["zero or more", "q"], [" times.", ""]]; export const EndPicker = ({value, set, label}) =>
{label}
{ENDS.map((e, j) => )}
; export const DECODER_MODEL = { ents: [{ id: "m", name: "Member", x: 20, y: 20, attrs: [] }, { id: "l", name: "Loan", x: 370, y: 20, attrs: [] }], links: [{ id: "ml", a: "m", as: "r", b: "l", bs: "l", ea: "one", eb: "zeroMany" }] }; export const ENDS = [{ kind: "one", name: "exactly one", min: 1, many: false }, { kind: "zeroOne", name: "zero or one", min: 0, many: false }, { kind: "oneMany", name: "one or more", min: 1, many: true }, { kind: "zeroMany", name: "zero or more", min: 0, many: true }]; export const HERO_STEPS = [{ part: "n", label: "Show the nouns", keys: ["e:m", "e:l"], note: "Nouns become entities: boxes with singular names, Member and Loan." }, { part: "v", label: "Show the verb", keys: ["l:ml"], note: "The verb becomes a relationship: a line between the boxes, labelled “places”." }, { part: "q", label: "Show the quantities", keys: ["c:ml"], note: "Quantities become symbols at the ends of the line. Two bars next to Member mean exactly one. A circle and a crow's foot next to Loan mean zero or more." }]; export const HERO_WORDS = [["Each ", ""], ["member", "n"], [" ", ""], ["places", "v"], [" ", ""], ["zero or more", "q"], [" ", ""], ["loans", "n"], [". Each loan ", ""], ["belongs to", "v"], [" ", ""], ["exactly one", "q"], [" member.", ""]]; export const HERO_MODEL = { ents: [{ id: "m", name: "Member", x: 20, y: 34, attrs: [] }, { id: "l", name: "Loan", x: 390, y: 34, attrs: [] }], links: [{ id: "ml", a: "m", as: "r", b: "l", bs: "l", ea: "one", eb: "zeroMany", label: "places" }] }; export const erdTable = ({t, rowCls = () => "", minWidth}) => { const key = t.key || []; const fk = t.fk || []; return
{t.cols.map((h, j) => )} {t.rows.map((r, i) => {r.map((v, j) => )} )}
{t.name}
{h}
{v}
; }; export const erdSvg = (m, st = () => "") => { const bw = m.bw || 150; const esc = s => String(s).replace(/&/g, "&").replace(/ { const y = e.y + 41 + i * 20; const key = k ? `${esc(k)}` : ""; return `${esc(name)}${key}`; }).join(""); const sep = n ? `` : ""; boxes += `` + `${esc(e.name)}${sep}${attrs}`; } const side = (b, s) => { const ay = Math.min(36, b.h / 2); if (s === "r") return [b.x + bw, b.y + ay]; if (s === "l") return [b.x, b.y + ay]; if (s === "t") return [b.x + bw / 2, b.y]; return [b.x + bw / 2, b.y + b.h]; }; let lines = ""; for (const k of m.links) { const pa = side(box[k.a], k.as); const pb = side(box[k.b], k.bs); const da = dirs[k.as]; const db = dirs[k.bs]; const qa = [pa[0] + da[0] * CUT, pa[1] + da[1] * CUT]; const qb = [pb[0] + db[0] * CUT, pb[1] + db[1] * CUT]; const ha = da[1] === 0; const hb = db[1] === 0; let d = `M${qa[0]} ${qa[1]}`; if (ha && hb) d += qa[1] === qb[1] ? ` H${qb[0]}` : ` H${(qa[0] + qb[0]) / 2} V${qb[1]} H${qb[0]}`; else if (!ha && !hb) d += qa[0] === qb[0] ? ` V${qb[1]}` : ` V${(qa[1] + qb[1]) / 2} H${qb[0]} V${qb[1]}`; else if (ha) d += ` H${qb[0]} V${qb[1]}`; else d += ` V${qb[1]} H${qb[0]}`; const lx = k.lx !== undefined ? k.lx : ha ? (qa[0] + qb[0]) / 2 : qa[0] + 8; const ly = k.ly !== undefined ? k.ly : ha ? Math.min(qa[1], qb[1]) - 13 : (qa[1] + qb[1]) / 2 + 4; const la = k.la || (ha ? "middle" : "start"); const label = k.label ? `${esc(k.label)}` : ""; const ends = st(`c:${k.id}`); const stubs = ``; lines += `${label}${stubs}` + `${erdEnd(pa, da, k.ea)}${erdEnd(pb, db, k.eb)}`; } return lines + boxes; }; export const erdGlyph = kind => `` + erdEnd([40, 10], [-1, 0], kind); export const erdEnd = (p, d, kind) => { const n = [-d[1], d[0]]; const f = v => Math.round(v * 10) / 10; const P = (t, s = 0) => [p[0] + d[0] * t + n[0] * s, p[1] + d[1] * t + n[1] * s]; const L = (a, b) => ``; const many = kind === "oneMany" || kind === "zeroMany"; const zero = kind === "zeroOne" || kind === "zeroMany"; let s = many ? L(P(0, 8), P(14)) + L(P(0, -8), P(14)) : L(P(7, 7), P(7, -7)); if (zero) { const c = many ? 22 : 17; const o = P(c); s += L(P(0), P(c - 4.5)); if (c + 4.5 < 26.5) s += L(P(c + 4.5), P(26.5)); s += ``; } else { const b = many ? 19 : 13; s += L(P(b, 7), P(b, -7)) + L(P(0), P(26.5)); } return s; }; export const useOnceVisible = (ref, onEnter) => { const cb = useRef(onEnter); cb.current = onEnter; useEffect(() => { const el = ref.current; if (!el) return; if (typeof IntersectionObserver === "undefined") { cb.current(); return; } const io = new IntersectionObserver(entries => { if (entries.some(e => e.isIntersecting)) { io.disconnect(); cb.current(); } }, { threshold: 0.35 }); io.observe(el); return () => io.disconnect(); }, [ref]); }; export const useReducedMotion = () => { const [reduce, setReduce] = useState(false); useEffect(() => { const mq = window.matchMedia("(prefers-reduced-motion: reduce)"); setReduce(mq.matches); const on = e => setReduce(e.matches); mq.addEventListener("change", on); return () => mq.removeEventListener("change", on); }, []); return reduce; }; > This is the Markdown version of an interactive page. Every animated figure on the web page is replaced here by a table, a list or Mermaid code with the same facts. Source: adapted from Lucidchart, ["What is an Entity Relationship Diagram (ERD)?"](https://lucid.co/diagram/erd/tutorial), with corrections. Someone hands you a paragraph: "Members borrow books. A book can have several authors. We need to know when each book is due back." Before you write a single `CREATE TABLE`, you need to know which tables there are and how they connect. Get it wrong, and you find out three months later when a book with two authors won't fit. An **entity relationship diagram (ERD)** is a drawing of that answer. Boxes are the things you store, lines are how they relate, and small symbols at the ends of each line say how many. It is a map of the data's *structure*. It is not a flowchart: a flowchart shows steps happening over time, and an ERD shows what exists and how it is connected. This post teaches you to draw one yourself. You will learn to read the symbols, follow a five-step recipe on a small library, fix the four most common mistakes, and then try one on your own. It helps to know what a table, a primary key and a foreign key are. If not, read [The Relational Model](/writing/database/relational-model) first. **In short** * An ERD has four parts: **entities** (boxes), **attributes** (lines of text inside each box), **relationships** (lines between boxes) and **cardinality** (the symbols at each end). * Read across the line. The symbol next to a box answers "how many of *this* box for one of the other?" The inner symbol is the maximum, the outer one is the minimum. * Recipe: find the nouns → pick the entities → add attributes and keys → draw lines for the verbs → set the cardinality. * Many-to-many (crow's feet on both ends) needs a **bridge table**. * The foreign key goes on the crow's-foot side. For one-to-one it goes on the optional side. ## The four parts Take this sentence: **"Each member places zero or more loans. Each loan belongs to exactly one member."** Every ERD is built from four kinds of part, and this sentence has all of them. * **Entity**: a kind of thing you store many of. The nouns *member* and *loan* become boxes, named in the singular: `Member` and `Loan`. * **Attribute**: a fact about one entity, such as a member's *name*. It is written as a line of text inside the box. (This sentence has none yet.) * **Relationship**: how two entities are linked. The verb *places* becomes a line between the boxes, labelled with the verb. * **Cardinality**: how many on each side. *Zero or more* and *exactly one* become symbols at the two ends of the line. The hero diagram, as Mermaid: ``` erDiagram MEMBER ||--o{ LOAN : places ``` `||` next to `MEMBER` means exactly one member per loan. `o{` next to `LOAN` means zero or more loans per member. An entity is a *kind* of thing (Member), not one thing (Ana). Ana is one row in the `Member` table later on. Name boxes in the singular, because each box describes one row. ## Reading crow's-foot notation There are several ways to draw cardinality. This post uses **crow's foot**, the style most developer tools use: dbdiagram.io, Mermaid, DBeaver, MySQL Workbench and Lucidchart all draw it. Each end of a line carries two symbols: * **Inner symbol, next to the box: the maximum.** A bar `|` means one. A crow's foot `<` (three prongs) means many. * **Outer symbol, further along the line: the minimum.** A bar `|` means one, so the link is required. A circle `o` means zero, so it is optional. To read a line, **start at one box, cross the line, and read the symbol at the far end**. Start at Member and read the symbol next to Loan: "Each member has zero or more loans." Then start at Loan and read the symbol next to Member: "Each loan belongs to exactly one member." Try other symbols and see which real situations each one allows. The four end symbols: | Symbol at the end | Drawn as | Min | Max | Reads as | Mermaid (left / right of `--`) | | - | - | - | - | - | - | | Two bars | `\|\|` | 1 | 1 | exactly one | `\|\|` / `\|\|` | | Circle + bar | `o\|` | 0 | 1 | zero or one | `\|o` / `o\|` | | Bar + crow's foot | `\|<` | 1 | many | one or more | `}\|` / `\|{` | | Circle + crow's foot | `o<` | 0 | many | zero or more | `}o` / `o{` | What each choice allows on the Member–Loan line: | Situation | Allowed when… | | - | - | | Ana has no loans yet | the symbol next to **Loan** has a circle (minimum zero) | | Ana borrows a second book | the symbol next to **Loan** is a crow's foot (maximum many) | | A loan with no member | the symbol next to **Member** has a circle | | One loan shared by Ana and Ben | the symbol next to **Member** is a crow's foot | What the pair of maximums means for the tables: | Next to Member | Next to Loan | Shape | Tables | | - | - | - | - | | one | many | one-to-many | `Loan.memberID` is a foreign key. `NOT NULL` if the minimum next to Member is one, nullable if zero. | | one | one | one-to-one | `Loan.memberID` with `UNIQUE`. | | many | one | one-to-many, reversed | `Member.loanID` (legal, but odd for a library). | | many | many | many-to-many | A bridge table holding `memberID` and `loanID`. | The library's real rule: exactly one next to Member, zero or more next to Loan. A trick to remember the minimum: the circle looks like a zero. The bar looks like a one. Some tools write the same rules as numbers instead, in UML style: `1` (exactly one), `0..1` (zero or one), `1..*` (one or more), `0..*` (zero or more). The older **Chen** notation, from Peter Chen's 1976 paper that introduced ER modelling, draws relationships as diamonds and attributes as ovals. It is good for teaching concepts, but it takes a lot of space, so developer tools rarely use it. The ideas are the same in all three styles. Only the symbols change. ## The recipe: from a brief to a diagram Here is a short brief for a library system. You will rarely get a cleaner one. Follow five passes over it. Each pass looks for one kind of word and adds one kind of part to the drawing. The brief: > A library lends books to members. Each member has a name and an email. Each book has a title and an ISBN. A book is written by one or more authors, and an author writes one or more books. Each author has a name. When a member borrows a book, the library records a loan with the date borrowed and the due date. A member can have zero or more loans. Each loan is for exactly one book and exactly one member. A book can be loaned zero or more times. | Step | Look for | Found | Add to the diagram | | - | - | - | - | | 1. Find the nouns | every noun | library, book, member, name, email, title, ISBN, author, loan, date borrowed, due date | nothing yet; these are candidates | | 2. Pick the entities | nouns with many instances and facts of their own | Member, Book, Author, Loan. *Library* is the whole system (there is one), so it is not an entity. *Loan* began as the verb "borrows", but it has its own facts (two dates), so it becomes an entity. | four boxes | | 3. Add attributes and keys | nouns that describe an entity | Member: name, email. Book: title, ISBN. Author: name. Loan: borrowedOn, dueOn. | attributes, plus an ID primary key per entity | | 4. Draw the relationships | verbs that link two entities | member makes loans, loan is for a book, book is written by authors | three labelled lines | | 5. Set the cardinality | quantity words; ask "how many?" both ways | see the Mermaid below | symbols at both ends | The diagram after step 5: ``` erDiagram MEMBER ||--o{ LOAN : makes BOOK ||--o{ LOAN : "is for" BOOK }|--|{ AUTHOR : "written by" MEMBER { int memberID PK string name string email } LOAN { int loanID PK date borrowedOn date dueOn } BOOK { int bookID PK string title string isbn } AUTHOR { int authorID PK string name } ``` A few rules of thumb for each step: 1. **Nouns.** Mark them all, even the obvious ones. It is faster to cross a noun off than to notice one you missed. 2. **Entities.** Keep a noun if there are many of it and it has facts of its own. Drop the system itself ("the library", "the app"). Watch for verbs that carry facts: "borrows" has dates, so it becomes the `Loan` entity. Orders, bookings, payments and enrolments are often found this way. 3. **Attributes.** A noun that only describes something else is an attribute. Give every entity a primary key that never changes. A plain ID such as `memberID` is the safe default. 4. **Relationships.** One line per pair of entities that the brief links directly. Label it with the verb, so the line reads as a sentence: *Member makes Loan*. 5. **Cardinality.** For each line, ask the question twice: "One member has how many loans?" and "One loan has how many members?" Draw each answer **next to the box being counted**. If the brief doesn't say, ask whoever wrote it. That question is often the most useful thing the diagram does. Notice that the diagram has no foreign keys yet. That is on purpose. At this stage the drawing is about meaning. Foreign keys come next, and the lines tell you where they go. ## Many-to-many needs a bridge Look at the Book–Author line. It has a crow's foot on **both** ends: a book has one or more authors, and an author writes one or more books. Lines like this can't be stored as drawn. Try with real data: *Good Omens* was written by Terry Pratchett and Neil Gaiman. **One foreign key (fails).** `Book(bookID, title, authorID)`: | bookID | title | authorID | | - | - | - | | 1 | Good Omens | 1 | | ~~1~~ | ~~Good Omens~~ | ~~2~~ (refused: repeats bookID 1) | | 2 | Mort | 1 | `Author`: (1, Terry Pratchett), (2, Neil Gaiman). Putting `bookID` in `Author` fails the same way, because Pratchett wrote two books. **Bridge table (works).** | Book: bookID | title | | - | - | | 1 | Good Omens | | 2 | Mort | | BookAuthor: bookID | authorID | | - | - | | 1 | 1 | | 1 | 2 | | 2 | 1 | | Author: authorID | name | | - | - | | 1 | Terry Pratchett | | 2 | Neil Gaiman | ``` erDiagram BOOK ||--|{ BOOK_AUTHOR : has AUTHOR ||--|{ BOOK_AUTHOR : "named in" ``` A `Book` row has one `authorID` cell, so a second author needs a second row, and that row repeats the book's primary key. The fix is a third table, often called a **bridge**, **junction** or **associative** table. It has one row per book–author pair, and both of its columns are foreign keys. In the diagram, the bridge turns one many-to-many line into **two one-to-many lines**, with the crow's feet pointing at the bridge. The bridge can also hold facts about the pair itself. For example, `BookAuthor.position` would store whether Gaiman is listed first or second. You have already met one of these. `Loan` sits between `Member` and `Book`, and over time a member borrows many books and a book is borrowed by many members. `Loan` is a bridge with its own facts (the dates), which is why it showed up as an entity in step 2. ## Where the foreign key goes Once every line is one-to-many or one-to-one, the diagram tells you exactly where each foreign key goes. The minimum symbols tell you whether it can be empty. | Shape | Example | Foreign key goes | SQL | | - | - | - | - | | One-to-many | `MEMBER \|\|--o{ LOAN` | On the crow's-foot side: `Loan.memberID`. Bar next to Member (minimum one) → `NOT NULL`. | `memberID INT NOT NULL REFERENCES Member(memberID)` | | One-to-one | `MEMBER \|\|--o\| LIBRARY_CARD` | On the optional side: `LibraryCard.memberID`, with `UNIQUE`. | `memberID INT NOT NULL UNIQUE REFERENCES Member(memberID)` | | Many-to-many | `BOOK }\|--\|{ AUTHOR` | In a bridge table: `BookAuthor(bookID, authorID)`, both foreign keys, together the primary key. | `PRIMARY KEY (bookID, authorID)` | ```sql theme={null} CREATE TABLE Loan ( loanID INT PRIMARY KEY, memberID INT NOT NULL REFERENCES Member(memberID), dueOn DATE NOT NULL ); CREATE TABLE LibraryCard ( cardID INT PRIMARY KEY, memberID INT NOT NULL UNIQUE REFERENCES Member(memberID), issuedOn DATE NOT NULL ); CREATE TABLE BookAuthor ( bookID INT NOT NULL REFERENCES Book(bookID), authorID INT NOT NULL REFERENCES Author(authorID), PRIMARY KEY (bookID, authorID) ); ``` The rule in one line: **the foreign key lives where the crow's foot is.** Each loan has one member, so the loan can store it. A member has many loans, so the member can't. The minimums map to SQL too, but only partly: | Minimum on the diagram | What SQL can enforce | | - | - | | Bar next to the "one" side (every loan **must** have a member) | `NOT NULL` on the foreign key | | Circle next to the "one" side (a loan **may** have no member) | leave the foreign key nullable | | Circle next to the "many" side (a member may have **no** loans) | nothing to do; that is the default | | Bar next to the "many" side (every book has **at least one** author) | no plain constraint does this; check it in the app, a trigger or a transaction | That last row surprises people. A foreign key can say "this loan points at a real member". It can't say "this book must have at least one row in another table". Draw the rule anyway, because the diagram is where you agree on it. Then decide who enforces it. ## Four common mistakes These four show up in nearly every first ERD. Each card shows the mistake, and one click shows the fix. | Mistake | Looks like | Why it's wrong | Fix | | - | - | - | - | | An attribute drawn as an entity | `Member \|\|--\|\| Email` | Email has no facts of its own; it only describes a member. | Make `email` an attribute of `Member`. Exception: if a member can have many emails, `Email` is an entity with a one-to-many line. | | Many-to-many with no bridge | `Book(bookID, title, authorID)` with crow's feet on both ends | `Book` has room for one `authorID`. | Add `BookAuthor(bookID, authorID)`. | | Foreign key on the "one" side | `Member(memberID, name, loanID)` with `Member \|\|--o{ Loan` | A member row has one `loanID` cell but many loans. | Move it: `Loan(loanID, memberID, dueOn)`. | | Symbols at the wrong end | `BOOK }o--\|\| LOAN` for "each loan is for one book; a book can be loaned many times" | The drawing says each book is loaned exactly once and a loan can cover many books. | `BOOK \|\|--o{ LOAN`: the symbol next to Loan answers "how many loans per book?". | The fourth mistake is the most common one. It happens when you draw the symbol next to the box you *start from*, instead of the box you are *counting*. When in doubt, read the line out loud from both ends. ## Your turn Here is a new brief. Answer the four questions, then check your answers. If you get stuck, scroll back to the recipe. The brief: > A small clinic has doctors and patients. Each doctor and each patient has a name. A patient books appointments. Each appointment is with exactly one doctor and exactly one patient, at a set date and time. A new doctor may have no appointments yet; a busy one has many. A patient can have zero or more appointments. | Question | Answer | Why | | - | - | - | | Which nouns are entities? (Clinic, Doctor, Patient, Appointment, Date, Time) | Doctor, Patient, Appointment | They have many instances and facts of their own. The clinic is the system. Date and time are an attribute of an appointment (`startsAt`). | | On the Doctor–Appointment line, which symbol goes next to Appointment? | zero or more (`o<`) | It answers "how many appointments per doctor?". A new doctor has none. | | Which symbol goes next to Doctor? | exactly one (`\|\|`) | Each appointment is with one doctor. | | Where does `doctorID` go? | In `Appointment` | Foreign key on the crow's-foot side. `patientID` goes there for the same reason. | Solution: ``` erDiagram DOCTOR ||--o{ APPOINTMENT : has PATIENT ||--o{ APPOINTMENT : books DOCTOR { int doctorID PK string name } PATIENT { int patientID PK string name } APPOINTMENT { int appointmentID PK int doctorID FK int patientID FK datetime startsAt } ``` `Appointment` is a bridge between `Doctor` and `Patient` (many-to-many over time) with facts of its own, just like `Loan`. ## From diagram to code Most teams don't draw ERDs by hand any more. They write them as text, so the diagram lives next to the code and shows up in code review. Here is the finished library, with the bridge table and every foreign key, in three common formats. **Mermaid.** GitHub, GitLab, Notion and this site render it straight from Markdown: ```mermaid theme={null} erDiagram MEMBER ||--o{ LOAN : makes BOOK ||--o{ LOAN : "is for" BOOK ||--|{ BOOK_AUTHOR : has AUTHOR ||--|{ BOOK_AUTHOR : "named in" MEMBER { int memberID PK string name string email } LOAN { int loanID PK int memberID FK int bookID FK date borrowedOn date dueOn } BOOK { int bookID PK string title string isbn } BOOK_AUTHOR { int bookID PK, FK int authorID PK, FK } AUTHOR { int authorID PK string name } ``` **DBML.** This is the format of [dbdiagram.io](https://dbdiagram.io), which draws the diagram and exports SQL: ```text theme={null} Table Member { memberID int [pk] name varchar [not null] email varchar [not null, unique] } Table Book { bookID int [pk] title varchar [not null] isbn varchar [unique] } Table Author { authorID int [pk] name varchar [not null] } Table Loan { loanID int [pk] memberID int [not null, ref: > Member.memberID] bookID int [not null, ref: > Book.bookID] borrowedOn date [not null] dueOn date [not null] } Table BookAuthor { bookID int [ref: > Book.bookID] authorID int [ref: > Author.authorID] indexes { (bookID, authorID) [pk] } } ``` **SQL.** This is what the database actually runs: ```sql theme={null} CREATE TABLE Member ( memberID INT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE ); CREATE TABLE Book ( bookID INT PRIMARY KEY, title VARCHAR(200) NOT NULL, isbn VARCHAR(17) UNIQUE ); CREATE TABLE Author ( authorID INT PRIMARY KEY, name VARCHAR(100) NOT NULL ); CREATE TABLE Loan ( loanID INT PRIMARY KEY, memberID INT NOT NULL REFERENCES Member(memberID), bookID INT NOT NULL REFERENCES Book(bookID), borrowedOn DATE NOT NULL, dueOn DATE NOT NULL ); CREATE TABLE BookAuthor ( bookID INT NOT NULL REFERENCES Book(bookID), authorID INT NOT NULL REFERENCES Author(authorID), PRIMARY KEY (bookID, authorID) ); ``` You can go the other way too. DBeaver, DataGrip, MySQL Workbench and pgAdmin can draw an ERD from an existing database's foreign keys. That is a quick way to learn a codebase you didn't design. ### How much detail? The same model can be drawn at three levels of detail. Pick the level that suits the reader. * **Conceptual**: entity names and lines, maybe cardinality. Use it to agree on scope with people who don't write code. In this post: recipe steps 2 and 4. * **Logical**: adds attributes, primary keys, cardinality and bridge tables. Use it to agree on the design. In this post: recipe step 5, plus the bridge. * **Physical**: adds column types, `NOT NULL`, foreign keys and indexes. Use it to build. In this post: the SQL above. ### When an ERD is the wrong tool * **Process questions.** "What happens after a book is returned?" is a flow, so draw a flowchart or sequence diagram. An ERD has no time and no order. * **Data with no fixed shape.** Free-form documents, logs or user-defined fields don't fit fixed boxes well. A rough sketch still helps, but don't force it. * **Rules beyond cardinality.** "A member can have at most five loans at once" is a real rule, but crow's foot can't draw it. Write it as a note next to the line. Once the diagram is drawn, check each table against the normal forms in [Designing a Relational Database](/writing/database/relational-database-design). That catches the facts that are still stored twice. ## Check yourself Questions this document answers: * What is an ERD? A diagram of the data's structure: entities (boxes), attributes (fields inside), relationships (lines) and cardinality (end symbols). It is not a flowchart; it has no time or order. * How do you read crow's-foot notation? Start at one box, cross the line, and read the symbol next to the other box. The inner symbol is the maximum (bar = one, crow's foot = many); the outer is the minimum (circle = zero, bar = one). * What does `o<` next to Loan mean on a Member–Loan line? Each member has zero or more loans. * How do you turn a brief into an ERD? Find the nouns, pick the entities, add attributes and primary keys, draw a labelled line for each linking verb, then ask "how many?" both ways for each line. * Is every noun an entity? No. Entities have many instances and facts of their own. The system itself and nouns that only describe something (name, email, date) are not entities. * How do you store a many-to-many relationship? With a bridge table that holds both foreign keys, such as `BookAuthor(bookID, authorID)`. Its primary key is the pair. * Where does the foreign key go? On the crow's-foot ("many") side for one-to-many; on the optional side, with `UNIQUE`, for one-to-one; in a bridge table for many-to-many. * How do minimums map to SQL? A required "one" side becomes `NOT NULL` on the foreign key. "At least one" on the many side can't be enforced by a plain constraint. * What are the most common ERD mistakes? Attributes drawn as entities, many-to-many without a bridge, the foreign key on the "one" side, and cardinality symbols drawn at the wrong end. * Which notations exist? Crow's foot (most tools), UML multiplicities (`1`, `0..1`, `1..*`, `0..*`), and Chen (diamonds for relationships, ovals for attributes). * How do you write an ERD as code? Mermaid `erDiagram`, DBML for dbdiagram.io, or SQL DDL; examples above. * What are conceptual, logical and physical models? The same design at increasing detail: names and lines; plus attributes, keys and cardinality; plus types and constraints. ## Glossary | Term | Meaning | | - | - | | ERD | Entity relationship diagram: a drawing of what data is stored and how it connects. | | Entity | A kind of thing stored many times, drawn as a box. Becomes a table. | | Attribute | A fact about one entity, drawn inside its box. Becomes a column. | | Relationship | A link between two entities, drawn as a labelled line. | | Cardinality | How many of one entity relate to one of the other: the end symbols. | | Crow's foot | The three-pronged end symbol meaning "many"; also the name of the notation. | | Minimum / maximum | The outer (circle = 0, bar = 1) and inner (bar = 1, crow's foot = many) symbols at a line's end. | | One-to-many | One row on one side, many on the other. Foreign key on the many side. | | Many-to-many | Many on both sides. Needs a bridge table. | | Bridge table | A table with one row per related pair, keyed by both foreign keys. Also called a junction or associative table. | | Primary key (PK) | The column(s) that pick out exactly one row. | | Foreign key (FK) | A column whose value must match a primary key in another table. | | Conceptual / logical / physical model | The same design at three levels of detail, from names only to full SQL. | ## For AI agents You are reading the Markdown export of `/writing/database/entity-relationship-diagrams`. The web page has the same text plus interactive figures; nothing factual is missing here. The site index for agents is at `/llms.txt`. Related pages: `/writing/database/relational-database-design` (keys, relationships, normalization) and `/writing/database/relational-model` (relations, keys, constraints). *** Adapted from Lucidchart, ["What is an Entity Relationship Diagram (ERD)?"](https://lucid.co/diagram/erd/tutorial). Changes: an ERD is described here as a structure diagram, not a type of flowchart; product-specific steps and the history before Chen's 1976 paper are left out; the rule that "one or more" can't be enforced by a plain foreign key is added. The library and clinic data are illustrative; figures are simplified teaching models. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.