Normalization splits data into tables; relationships are how the pieces connect again. A relationship is stored as a column in one table holding the key of a row in another — a foreign key — and the foreign-key constraint makes the database guarantee that the reference is valid. This lesson covers the three cardinalities (one-to-one, one-to-many, many-to-many), where the key goes in each, self-referencing tables, what ON DELETE and ON UPDATE actions do, and how to read a relationship back with joins.

One-to-many

The most common shape. A customer has many orders; each order has one customer. The key goes on the many side:

CREATE TABLE customers (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL
);

CREATE TABLE orders (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  placed_on   DATE NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers (id)
);

Every order row names its customer. The reverse — a list of order ids in the customer row — would be a repeating group, breaking 1NF. NOT NULL on customer_id makes the relationship mandatory (an order must have a customer); a nullable foreign key makes it optional (a ticket may or may not have an assignee). Cardinality and optionality are the two questions to ask of every relationship.

Reading it back is a join, and a LEFT JOIN when the "one" side may have no children:

SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Many-to-many

A student takes many courses; a course has many students. Neither table can hold the other's key, so a third table — a junction (bridge, associative, link) table — holds one row per pairing:

CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(80) NOT NULL);
CREATE TABLE courses  (id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(10) NOT NULL UNIQUE);

CREATE TABLE enrolments (
  student_id  INT  NOT NULL,
  course_id   INT  NOT NULL,
  enrolled_on DATE NOT NULL,
  grade       CHAR(2),
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students (id),
  FOREIGN KEY (course_id)  REFERENCES courses (id)
);

The composite primary key stops a duplicate pairing and indexes lookups by student; add an index on course_id for lookups the other way. Facts about the pairing itself — when the student enrolled, the grade — belong in the junction table, because they depend on both keys. A many-to-many relationship is really two one-to-many relationships meeting at the junction. Reading it back is two joins:

SELECT s.name, c.code
FROM enrolments e
JOIN students s ON s.id = e.student_id
JOIN courses  c ON c.id = e.course_id
ORDER BY s.name, c.code;

One-to-one

Each user has at most one profile. Model it with a foreign key that is also unique — most simply, by making the child's primary key the foreign key:

CREATE TABLE user_profiles (
  user_id INT PRIMARY KEY,
  bio     TEXT,
  avatar  VARCHAR(255),
  FOREIGN KEY (user_id) REFERENCES users (id)
);

Why not just put the columns in users? One-to-one splits are used for columns that are large or rarely read (kept off the hot table), for sensitive data with different access rules, and for optional extensions (only some users are sellers, so seller_details exists only for them). Without one of those reasons, one table is simpler.

Self-referencing relationships

A foreign key can point at its own table, which models a hierarchy:

CREATE TABLE employees (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(80) NOT NULL,
  manager_id INT,
  FOREIGN KEY (manager_id) REFERENCES employees (id)
);

The head of the company has manager_id NULL. One level is read with a self join (LEFT JOIN employees m ON m.id = e.manager_id); the whole tree with a recursive CTE, as in the CTE module. Categories with subcategories, threaded comments and bill-of-materials parts are the same shape.

Foreign key actions

When a referenced row is deleted or its key changes, the constraint's actions decide what happens to the rows that reference it:

FOREIGN KEY (customer_id) REFERENCES customers (id)
  ON DELETE RESTRICT
  ON UPDATE CASCADE

Choose per relationship. Order lines belong to their order: ON DELETE CASCADE. Orders are financial records that must be kept: RESTRICT, so a customer with orders cannot be deleted by accident. A ticket's assignee leaves: SET NULL. ON UPDATE CASCADE matters only if keys change, which surrogate keys never do.

Facts about InnoDB foreign keys interviewers like: both columns must have compatible types (an INT cannot reference a BIGINT UNSIGNED); the referenced column must be indexed, usually the primary key; InnoDB adds an index on the referencing column if none exists; and SET FOREIGN_KEY_CHECKS = 0 turns checking off for a session, which bulk loads use and which can leave orphans behind. The practice engine here accepts the declarations but does not enforce them, so these exercises keep references valid themselves.

Polymorphic references: a warning

A comments table with target_type ('post' or 'photo') and target_id cannot have a foreign key — the id points into different tables depending on the type — so the database cannot keep it valid. Prefer one nullable foreign key per possible parent (post_id, photo_id, with a CHECK that exactly one is set), or a junction table per parent type.

Key takeaways

• One-to-many: the foreign key goes on the many side; NOT NULL makes it mandatory.
• Many-to-many: a junction table with a composite primary key and the pairing's own attributes.
• One-to-one: a unique foreign key, often the child's primary key; use it for a reason.
• Self-references model hierarchies; the root has NULL.
• ON DELETE RESTRICT | CASCADE | SET NULL per relationship; RESTRICT is the default.
• Polymorphic id columns cannot be foreign keys; avoid them.