Lesson 4 of 22Beginner15 min

Relationships

By the end of this lesson

Model one-to-many and many-to-many relationships between tables.

A relationship is a rule about how many. One customer has many orders. One order contains many products, and one product appears on many orders. Getting these counts right decides your table structure, and getting them wrong is expensive to undo once there is data.

There are only four shapes to learn, and three of them are common.

The four shapes:

One-to-many: One customer, many orders. By far the most common. The foreign key goes on the many side: orders holds customer_id.
Many-to-many: Many orders contain many products. Neither table can hold the key, so a third table sits between them — order_items.
One-to-one: One employee has one set of payroll details. Uncommon, and usually a sign the columns belong in the same table unless you are separating them for access control or because they are rarely read.
Self-referencing: One employee reports to another employee. The foreign key points back at the same table — employees.manager_id references employees.employee_id.

One-to-many: the key goes on the many side

This is the rule worth memorising, because reversing it is the most common structural error. The order knows which customer it belongs to. The customer does not hold a list of orders.

Why not the other way round? A column holds one value. If customers held an order_id, a customer could have exactly one order. Putting customer_id on orders means a customer can have none, one, or ten thousand, with no change to the table.

One customer, many orders — the foreign key lives on the orders side

One-to-many and self-referencing, declared

SQL

-- One customer, many orders: customer_id lives on orders
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id);

-- One employee reports to one manager, who is also an employee
ALTER TABLE employees
    ADD CONSTRAINT fk_employees_manager
    FOREIGN KEY (manager_id) REFERENCES employees (employee_id);

ALTER TABLE adds a constraint to a table that already exists, which is how you add relationships to a live schema.
The second constraint references the same table it is declared on. That is allowed, and it is how reporting lines, category trees and threaded comments are stored.
manager_id must stay nullable for this to work. Whoever is at the top of the reporting line has no manager, and a NOT NULL column would have no valid value for them.

Many-to-many needs a third table

An order contains several products. Each product appears on many orders. Neither table can hold a single value that captures that, so you create a table whose whole purpose is to record pairings. It is usually called a junction or join table.

order_items is one of these, and it shows the main reason these tables are worth understanding: the pairing itself carries data. How many were bought, and at what price, is a fact about this product on this order. It has nowhere else to live.

The junction table, and the question it makes answerable

SQL

-- order_items pairs one order with one product, and records the pairing's own data
SELECT
    o.order_id,
    p.product_name,
    oi.quantity,
    oi.unit_price
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products    AS p  ON p.product_id = oi.product_id
WHERE o.order_id = 1043;

Reading a many-to-many relationship always takes two joins: from one side into the junction table, and out to the other side.
Joins are covered properly later in the course. For now, read JOIN ... ON as "line these rows up where these columns match".
quantity and unit_price sit on order_items because they describe the pairing. They are not facts about the product, and they are not facts about the order as a whole.

Reading a relationship in both directions

Before writing a table, say the relationship out loud in both directions. The awkward direction is where the mistakes hide:

One customer has many orders. One order belongs to one customer. → foreign key on orders.
One order has many order items. One order item belongs to one order. → foreign key on order_items.
One product appears on many order items. One order item refers to one product. → foreign key on order_items.
One employee has many direct reports. One employee has at most one manager. → nullable foreign key on employees.
One order is placed by at most one employee. One employee handles many orders. → nullable foreign key on orders.

Common mistake

Common early mistakes

Putting the foreign key on the one side. A customers.order_id column limits every customer to a single order, and the usual next step is a second column, order_id_2, which does not scale past the third order.

Modelling a many-to-many as repeated columns: product_1_id, product_2_id, product_3_id. Every query then has to check three columns, and the fourth product requires a schema change.

Making manager_id NOT NULL. There is no valid manager for the person at the top, so the first insert fails and the usual workaround is a fake employee row that then appears in every headcount.

Creating a one-to-one table out of habit. Splitting employees into employees and employee_details, both keyed by employee_id, means every read needs a join for no benefit. Split only when you have a reason: restricting access to salary data, or keeping a large rarely-read column out of the main table.

Summary

One-to-many is the common case, and the foreign key always goes on the many side
Many-to-many needs a junction table, which is also where data about the pairing belongs
A self-referencing foreign key models hierarchies such as reporting lines, and its column must be nullable
One-to-one is rare and usually means the columns belong together unless you are separating access or bulk

Practice

Attempt each one before opening the solution. Getting it wrong first is how the idea sticks.

Think about it

You need to record which employees speak which languages, and how fluent each one is. What tables do you need, and where does the fluency level live?

Show solution

An employee speaks many languages and a language is spoken by many employees, so this is many-to-many and needs a junction table: employee_languages, holding employee_id and language_id.

Fluency belongs on the junction table. It is not a fact about the employee (they may be fluent in one language and basic in another) and it is not a fact about the language. It describes the pairing.

This is the same shape as order_items, and recognising it is most of the skill. Once you see "the pairing has its own data", the junction table stops feeling like an extra table and starts looking like the thing you were trying to store.

SQL

CREATE TABLE languages (
    language_id INT IDENTITY(1,1) NOT NULL,
    name        NVARCHAR(60)      NOT NULL,
    CONSTRAINT pk_languages PRIMARY KEY (language_id),
    CONSTRAINT uq_languages_name UNIQUE (name)
);

CREATE TABLE employee_languages (
    employee_id INT          NOT NULL,
    language_id INT          NOT NULL,
    fluency     NVARCHAR(20) NOT NULL,
    CONSTRAINT pk_employee_languages PRIMARY KEY (employee_id, language_id),
    CONSTRAINT fk_emp_lang_employee FOREIGN KEY (employee_id)
        REFERENCES employees (employee_id),
    CONSTRAINT fk_emp_lang_language FOREIGN KEY (language_id)
        REFERENCES languages (language_id)
);

Challenge

Products belong to categories, and a category can contain sub-categories to any depth — Electronics contains Audio, which contains Headphones. Sketch the tables.

Then consider what makes "list every product anywhere under Electronics" harder than a normal one-to-many query.

Show solution

A categories table with a nullable parent_category_id referencing categories, and products holding a category_id. That is a self-referencing one-to-many, the same shape as employees.manager_id.

The hard part is depth. A single join reaches one level down. Electronics to Headphones is two levels, and the structure allows any number, so you cannot write a fixed number of joins.

Walking a tree of unknown depth needs a recursive query, which is a recursive common table expression. That is covered later in the course. Recognising that this shape requires recursion is the valuable part here.

SQL

CREATE TABLE categories (
    category_id        INT          NOT NULL,
    name               NVARCHAR(60) NOT NULL,
    parent_category_id INT          NULL,
    CONSTRAINT pk_categories PRIMARY KEY (category_id),
    CONSTRAINT fk_categories_parent FOREIGN KEY (parent_category_id)
        REFERENCES categories (category_id)
);

Knowledge check

Nothing is recorded and there is no score. The explanation appears either way.

In a one-to-many relationship between customers and orders, where does the foreign key go?

On customers, pointing at ordersOn orders, pointing at customersOn both tables, pointing at each otherIn a separate junction table

Why does a many-to-many relationship require a third table?

Because databases do not allow two foreign keys on one tableBecause a single column can hold only one value, so neither side can list the otherTo improve query performanceBecause primary keys must be unique

Saved in this browser only.