Chapter 6
Relate Tables Instead of Repeating Data
Let a Loan Point to Two Records
Riley has borrowed the cordless drill. The catalog already knows both records, but neither record should be copied into a new row.
items | ||
|---|---|---|
id | asset_tag | name |
1 | IT-100 | Cordless drill |
members | ||
|---|---|---|
id | member_code | name |
17 | MB-017 | Riley |
A loan can store the two IDs instead:
loans.id | item_id | member_id | checked_out_on | returned_on |
|---|---|---|---|---|
501 | 1 | 17 | 2026-08-18 | NULL |
Now follow the two numbers in the loan row. Each arrow ends at the matching id; loan 501 itself does not point anywhere:
Read across that last row. Each table’s id is its item_id is 1, which points back to the drill. member_id is 17, which points back to Riley. The loan’s own id, 501, identifies this borrowing event. Each ID has one job.
This keeps changing facts in one place. If Riley’s displayed name changes, the member row changes once. Past and current loans still point to member 17. If the drill’s name is clarified, its item row changes once for the same reason.
One member may have several loan rows over time. One item may also have several loan rows over time, because returned loans stay as history. This pattern is called a one-to-many relationship: one parent record can be connected to many related records.
The missing returned_on has a useful meaning too. The item has not been returned yet. That fact will soon help us find active loans. For now, concentrate on the shape: a loan carries its own facts and the two IDs that connect it to records we already have.
Which values belong in the row that records Riley borrowing the drill?