Part 3 of 5 · 2 min read
Reading the schema diagram
The five tables drawn together, and what each connecting line claims.
Think of the floor plan from an earlier module, now drawn for a slightly bigger building, with five rooms instead of two.
In plain words: drawing all five tables together shows two kinds of connection. books points at authors, a simple one-to-many link, since one author can write many books. orders points at customers, the same simple pattern. order_items sits between orders and books, pointing at both, which is how a many-to-many relationship is represented in a relational schema.
A worked example. The full diagram, in the plain-text style used earlier:
customers (customer_id PK, full_name, address)
authors (author_id PK, name)
books (book_id PK, author_id FK -> authors.author_id, title, price)
orders (order_id PK, customer_id FK -> customers.customer_id, placed_on)
order_items (order_id FK -> orders.order_id, book_id FK -> books.book_id, quantity,
PRIMARY KEY (order_id, book_id))
The key lines. A query that uses every table at once, to confirm the connections actually work together: the full list of books in one order, with their titles and authors.
SELECT b.title, a.name AS author, oi.quantity
FROM order_items oi
JOIN books b ON oi.book_id = b.book_id
JOIN authors a ON b.author_id = a.author_id
WHERE oi.order_id = 9001;
One-to-many, many-to-many
A one-to-many relationship means one row on one side can relate to many rows on the other, but each of those many rows relates back to only one. A many-to-many relationship means rows on both sides can relate to many rows on the other, and needs a junction table to represent correctly.
Example: authors to books is one-to-many; orders to books, through order_items, is many-to-many.
Remember this
Five tables, two kinds of connection: one-to-many links written as a direct foreign key, and many-to-many links written through a junction table. Nearly every later schema in this subject is a bigger version of exactly this pattern.