28 min read
One-to-Many
One member can have many loans. The foreign key sits on the many side — Loans.
One Member, Many Loans
Sophie Walsh of York is back at the desk in May. In January she borrowed The River Path — loan 101. Today she wants Northern Light. George Clarke writes loan 102. Same member. Second loan.
The next afternoon The River Path goes out again to Harry Cole. That is loan 103. One book, more than one loan record over time.
Each loan belongs to one member. But one member can have many loans. Sophie has 101 and 102.
Each loan is for one book. But one book can appear in many loan records over time.
That pair of counts is one-to-many. The word for those counts is cardinality: how many on this side, how many on that side.
| Sophie's loans | ||||
|---|---|---|---|---|
| The River Path over time | ||||
|---|---|---|---|---|
The Foreign Key on the Many Side
Members does not store a list of loan numbers. Each loan row stores one MemberID and one BookID. Those foreign keys sit on Loans — the many side.
Extra rows on Loans are how one-to-many grows. Extra columns such as Book1, Book2, Book3 on Members are not.
Members, loans, and books
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.
One-to-many on the ER diagram
Growing the many side
| Works | Breaks |
|---|---|
| A new loan row with MemberID 1 | A new column Book4 on Members |
| Foreign keys on Loans | A comma-separated list of titles in one cell |
Putting a list of books inside Members fails as soon as Sophie borrows a fourth title. One loan row per borrow, with MemberID and BookID on Loans, is the correct shape.
Practice
Swipe or use the arrows to move between questions.
1 / 3
More lessons in Database Fundamentals · Next: Many-to-Many · Previous: ER Diagrams