32 min read
Many-to-Many
Members borrow many books; books are borrowed by many members. Loans is the junction table.
Both Sides Repeat
Sophie Walsh has borrowed The River Path and Northern Light. One member, many books.
The River Path was borrowed by Sophie in January and by Harry Cole in May. One book, many members over time.
Oliver Bennett taps the desk. “Both sides repeat. You need a table in the middle.”
This is many-to-many. Each member can borrow many books. Each book can be borrowed by many members.
You cannot store that inside Members with Book1, Book2, Book3. You cannot store it inside Books with a comma-separated list of names. You need a third table whose job is the pairing.
What Does Not Work
| Members — repeating book columns (wrong) | ||||
|---|---|---|---|---|
The Junction Table
Each pairing becomes a row in Loans:
- Loan 101 — Sophie (1) and The River Path (1) on 3 Jan 2026
- Loan 102 — Sophie (1) and Northern Light (3) on 12 May 2026
- Loan 103 — Harry (2) and The River Path (1) on 13 May 2026
Loans holds a foreign key to Members, a foreign key to Books, and LoanDate — a fact about the pairing.
| Members | |||
|---|---|---|---|
| Books | ||
|---|---|---|
Click the table name, a column heading, or any value. Each piece has a job.
Two one-to-many lines, one junction
The bar marks the ONE side (the primary key). The three lines mark the MANY side (the foreign key). The line joins those two fields — it is not a flow of data.
Click a key icon, a field name, or a relationship line to highlight the matching fields.
Many-to-many through Loans
Comma-separated names in one cell fail as soon as two people borrow the same title on different dates. Add a Loans row per borrow. Each row stores MemberID, BookID, and LoanDate.
Practice
Swipe or use the arrows to move between questions.
1 / 3
More lessons in Database Fundamentals · Next: One-to-One · Previous: One-to-Many