Understanding Relationships: One-to-Many and More

Members holds one row per person. Loans holds one row per loan.

Sophie Walsh has two loan rows. Harry Cole (MemberID 2) has none in the sample data. That is still one-to-many — zero loans is allowed.

The foreign key always sits on the many side.

• Members.MemberID identifies each member once.
• Loans.MemberID repeats on every loan row for that member.

Do not store the member's name on Loans. Store MemberID 1. If Sophie Walsh changes her phone, you update Members once — every loan still points at MemberID 1.

You will learn: why many-to-many needs a junction table. how Loans sits between Members and Books. You will do: read the ER diagram. explain what Loans stores.

A many-to-many link means many rows on side A can match many rows on side B.

Many members borrow many books. You cannot put BookID on Members — one member borrows more than one book. You cannot put MemberID on Books — one book is borrowed by many members over time.

Loans is the junction table. Each loan row links one member to one book on one date.

Loan 101: MemberID 1 borrowed BookID 2 (Oliver Twist). Loan 102: MemberID 1 borrowed BookID 4 (The Hobbit).

Same member, two books — two loan rows. Same book can appear on many loan rows when different members borrow it.

Fines adds another one-to-many branch: one loan can have many fine rows (though the sample has at most one fine per loan).

The pattern is the same: put the foreign key on the many side. Fines.LoanID points at Loans.LoanID.

You will learn: what one-to-one means. when a separate table is useful. You will do: read the MemberCards example. see why the library rarely needs this pattern.

In a one-to-one relationship, one row on table A matches at most one row on table B.

At Riverside Public Library, each member gets one physical library card. The card number and issue date are extra facts about the member — but not every member needs many card rows.

MemberCards.MemberID is the primary key and also a foreign key to Members.MemberID.

That enforces one card per member: you cannot add two card rows with the same MemberID.

For a small library you could store CardNumber on Members instead. A separate table helps when card facts are optional, sensitive, or maintained by different staff.

The rest of this course uses one-to-many links: Members → Loans, Books → Loans, Loans → Fines. You do not need to build MemberCards unless your teacher asks.

One-to-many is by far the most common kind. One record in a "parent" table can relate to many records in a "child" table, but each record in the child table relates back to only one parent. One customer can place many orders, but each individual order belongs to exactly one customer — the Orders table holds a CustomerID field (the foreign key) matching the primary key of a specific customer in the Customers table.

Many-to-many covers the case where two things can each relate to many of the other — one student can take many classes, and one class can have many students. Access handles this with a third junction table in between, such as Enrollments, which contains a StudentID and a ClassID together, with each row representing one specific student's enrollment in one specific class.

One-to-one is uncommon: each record in one table relates to exactly one record in another, and vice versa. It's sometimes used to split a very wide table into two, or to keep sensitive information — salary details, say — in a separate, more tightly secured table.