Why data lives in several tables
Why the bookshop keeps authors and books apart, and how keys connect them again.
Lesson 24 of 33 · about 10 minutes
The lesson
Imagine keeping the whole bookshop in one big table, with the author's name and country typed out next to every book. Terry Pratchett would be written out twice, Ursula K. Le Guin three times.
That causes real trouble:
- Wasted effort. The same details are typed again and again.
- Mistakes creep in. One row says "Le Guin", another "LeGuin". Are they the same person?
- Changes are risky. If an author's country changes, you have to find and fix every copy. Miss one and your data disagrees with itself.
So the bookshop follows a simple rule: one fact, one place. Each author is written down once, in the authors table. Each book just points to its author with author_id, the foreign key you met in Module 2.
| books.title | books.author_id | authors.id | authors.name | |
|---|---|---|---|---|
| Small Gods | 4 | → | 4 | Terry Pratchett |
The cost is that one question can now need two tables. "Who wrote Small Gods?" means finding the book's author_id, then looking up that id in authors. Try it in two steps below. In the next lesson, JOIN will do both steps in one query.
- Keep each fact in one place, so it's typed once and changed once.
- Tables point to each other with keys: books.author_id matches authors.id.
- Answering a question can then mean looking in more than one table. JOIN does that for you.