Part 2 of 5 · 2 min read
The five starting tables
Customers, authors, books, orders and order_items, assembled as one schema.
Think of a single worked diagram that recurs through a textbook. Each new chapter adds one labelled detail, instead of the diagram being redrawn from scratch.
In plain words: the bookstore example starts with five tables. customers holds people who place orders. authors holds people who write books. books holds the catalog, each linked to one author. orders holds one row per order placed by a customer. order_items holds which books, and how many of each, belong to one order, since one order can contain more than one book.
A worked example. A customer places one order containing two different books. That order needs one row in orders, and two rows in order_items, one per book, each recording a quantity.
The key lines.
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
full_name TEXT NOT NULL,
address TEXT NOT NULL
);
CREATE TABLE authors (
author_id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE books (
book_id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author_id INTEGER NOT NULL REFERENCES authors(author_id),
price NUMERIC(6,2) NOT NULL
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
placed_on DATE NOT NULL
);
CREATE TABLE order_items (
order_id INTEGER NOT NULL REFERENCES orders(order_id),
book_id INTEGER NOT NULL REFERENCES books(book_id),
quantity INTEGER NOT NULL,
PRIMARY KEY (order_id, book_id)
);
order_items as a junction table
A junction table connects two tables that can each relate to many rows of the other. order_items is a junction table between orders and books, since one order can contain many books, and one book can appear in many orders.
Example: the composite primary key (order_id, book_id) on order_items is what makes it a proper junction table, rather than a table trying to force a one-to-one link where a many-to-many one actually exists.