Relational Modelling and Complex Joins

AQA 4.10.1, 4.10.3 — Relational modelling and complex joins

Retrieval: every query so far has treated one order as containing exactly one product (orders.product_id). Real orders usually contain several products — this lesson models that properly using the order_items table, and shows why the extra table is genuinely necessary, not just extra complexity for its own sake.

Key vocabulary

• One-to-many relationship — one row in table A relates to many rows in table B, but each row in B relates to only one row in A (one customer, many orders).
• Many-to-many relationship — rows in table A can relate to many rows in table B, and rows in table B can relate to many rows in table A (one order can contain many products; one product can appear in many orders).
• Junction table (associative table) — a table whose entire purpose is to represent a many-to-many relationship, holding a foreign key to each of the two related tables.

Understand — why a many-to-many relationship can't use a simple foreign key

A one-to-many relationship works with a single foreign key (orders.customer_id) because each order genuinely has only one customer. But an order can genuinely contain several products, and a product can genuinely appear in several orders — there is no single column that could hold "all of these products" for one order row. The solution is a junction table: order_items has its own primary key, a foreign key to orders, and a foreign key to products — each row represents one product's presence in one specific order, and an order can have as many order_items rows as it needs.

See it — the same order, modelled properly

Order 1 (Amara Okafor, placed 2026-01-04) genuinely contains two products:

Two rows, one order — exactly what a single orders.product_id column could never represent on its own.

See it — a complex, four-table join

SELECT customers.name, orders.id AS order_id, products.name AS product, order_items.quantity
FROM orders
JOIN customers ON orders.customer_id = customers.id
JOIN order_items ON order_items.order_id = orders.id
JOIN products ON order_items.product_id = products.id
ORDER BY orders.id;

Exam-style worked example 1

Question: Predict how many rows the query above returns for order 3, and name the products involved.

Model answer: Checking order_items for order_id = 3: two rows exist — product_id 2 (Mechanical Keyboard) and product_id 4 (USB-C Hub). 2 rows for order 3: Mechanical Keyboard and USB-C Hub.

Exam-style worked example 2

Question: Write a query to find the total revenue (price × quantity) generated by each order, using order_items and products, sorted highest revenue first.

Model answer:

SELECT order_items.order_id, SUM(products.price * order_items.quantity) AS revenue
FROM order_items
JOIN products ON order_items.product_id = products.id
GROUP BY order_items.order_id
ORDER BY revenue DESC;

Reason about why this reduces duplication and protects integrity

If an order's products were instead stored as a single text column like "Monitor, Mouse", the database could never reliably calculate a total price (it would have to parse text), enforce that each listed product genuinely exists, or handle an order with ten products cleanly. Splitting into order_items means each product reference is a genuine foreign key — automatically guaranteed to point at a real product — and each line item's quantity is a proper number the database can sum directly.

Debug it — diagnose, explain, fix, test, justify (incorrect join condition)

A student intends to link order items to their orders, but writes:

SELECT orders.id, products.name, order_items.quantity
FROM orders
JOIN order_items ON order_items.id = orders.id
JOIN products ON order_items.product_id = products.id;

This runs without error, but the results are wrong — items appear attached to the wrong orders.

1. Diagnose: should order_items be linked to orders via order_items.id, or via order_items.order_id?
2. Explain: what does order_items.id actually represent, and is it the same thing as "which order this item belongs to"?
3. Fix: correct the join condition.
4. Test: confirm order 1 now correctly shows its own two products (27" Monitor and Wireless Mouse), not items that happen to share a coincidental row-id match.
5. Justify: explain why this fault is easy to miss — the query runs, and produces a full, real-looking table.

(order_items.id is order_items' OWN primary key (uniquely identifying each line item row), not a reference to which order it belongs to - that's order_items.order_id's job. The fix changes the join condition to ON order_items.order_id = orders.id. This is easy to miss because the query is syntactically valid and returns a genuine, fully-populated table with real product and order data - only cross-checking specific rows against the known correct order/product pairings reveals the mismatch.)

Common mistake

Assuming a junction table is only needed for "complicated" relationships in general, rather than recognising the specific structural signal: whenever both directions of a relationship can genuinely be "many," a foreign key alone cannot represent it, and a junction table is required, not merely convenient.

Why this matters for the NEA

Recognising when data genuinely needs a junction table — rather than awkwardly cramming a list into a single column — is exactly the kind of relational design judgement AQA's NEA marking rewards in a project with any non-trivial data storage.

Check your understanding

A school's database needs to record that students can take multiple subjects, and each subject has multiple students. Identify what additional table (beyond students and subjects) is needed, name its columns, and explain why a simple foreign key on students alone would not work. (4 marks)

(A junction table, e.g. student_subjects (id, student_id, subject_id), is needed. A simple foreign key on students (e.g. students.subject_id) would only allow each student to be linked to ONE subject, which contradicts the requirement that students take MULTIPLE subjects - the relationship is genuinely many-to-many in both directions, which only a junction table with its own row per student-subject pairing can represent.)

Challenge

Write a query using order_items and customers (via orders) to find each customer's total number of distinct products ordered (not total quantity — the number of different products), sorted highest first.

Looking ahead: the next lesson covers what happens when multiple operations need to succeed or fail together — transactions — and how a database protects itself from becoming inconsistent.