Relationships — MySQL

MySQL Table Relationships

Relational databases organize data into related tables. The power of the relational model comes from how those tables are connected. MySQL supports three fundamental relationship types — one-to-one, one-to-many, and many-to-many — each implemented through foreign keys. Choosing the right relationship model for each part of your schema is one of the most impactful design decisions you will make.

One-to-One Relationship

A one-to-one (1:1) relationship means each row in Table A corresponds to at most one row in Table B, and vice versa. You could technically put all the data in a single table, so the reason to use a 1:1 split is usually one of:

Performance: separating rarely-accessed large columns (TEXT, BLOB) from the core row.
Security: isolating sensitive data (SSN, payment tokens) in a table with stricter access controls.
Optional extension: storing optional profile details separately from the required user record.

SQL

-- Core user record (read very frequently)
CREATE TABLE users (
  id            INT          NOT NULL AUTO_INCREMENT,
  email         VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

-- Extended profile (loaded only on profile page)
CREATE TABLE user_profiles (
  user_id    INT          NOT NULL,         -- FK and PK (enforces 1:1)
  bio        TEXT,
  avatar_url VARCHAR(500),
  birth_date DATE,
  website    VARCHAR(255),
  PRIMARY KEY (user_id),
  CONSTRAINT fk_profile_user
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

-- Query: join on demand
SELECT u.email, p.bio, p.avatar_url
FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id
WHERE u.id = 42;

Note

The child table uses the parent PK as both its own PK and as a FK. This is what enforces the 1:1 cardinality — a user can only appear once in user_profiles because user_id is the primary key there.

One-to-Many Relationship

A one-to-many (1:N) relationship is by far the most common pattern in relational databases. One row in the parent table can be referenced by many rows in the child table. The FK lives on the "many" side (the child).

Examples: one customer has many orders, one category has many products, one post has many comments.

SQL

-- Parent: one customer
CREATE TABLE customers (
  id    INT          NOT NULL AUTO_INCREMENT,
  name  VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  PRIMARY KEY (id)
);

-- Child: many orders per customer
CREATE TABLE orders (
  id          INT            NOT NULL AUTO_INCREMENT,
  customer_id INT            NOT NULL,   -- FK on the "many" side
  total       DECIMAL(10, 2) NOT NULL,
  status      VARCHAR(20)    NOT NULL DEFAULT 'pending',
  created_at  DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  CONSTRAINT fk_order_customer
    FOREIGN KEY (customer_id) REFERENCES customers (id)
    ON DELETE RESTRICT  -- prevent deleting customers with orders
);

-- Fetch a customer's orders
SELECT o.id, o.total, o.status
FROM orders o
WHERE o.customer_id = 42
ORDER BY o.created_at DESC;

-- Count orders per customer
SELECT c.name, COUNT(o.id) AS order_count, SUM(o.total) AS lifetime_value
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY lifetime_value DESC;

Many-to-Many Relationship

A many-to-many (M:N) relationship occurs when each row in Table A can relate to many rows in Table B, and vice versa. Examples: students enrolled in courses, products belonging to multiple categories, users with multiple roles.

You cannot implement M:N directly with a FK. Instead, you create a junction table (also called a bridge table, associative table, or link table) that holds one FK to each side.

SQL

-- Side A: students
CREATE TABLE students (
  id   INT          NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);

-- Side B: courses
CREATE TABLE courses (
  id    INT          NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

-- Junction table: one row per student-course enrollment
CREATE TABLE enrollments (
  student_id  INT      NOT NULL,
  course_id   INT      NOT NULL,
  enrolled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  grade       CHAR(2),
  PRIMARY KEY (student_id, course_id),   -- composite PK prevents duplicates
  CONSTRAINT fk_enroll_student FOREIGN KEY (student_id) REFERENCES students (id) ON DELETE CASCADE,
  CONSTRAINT fk_enroll_course  FOREIGN KEY (course_id)  REFERENCES courses  (id) ON DELETE CASCADE
);

-- All courses for a student
SELECT c.title, e.enrolled_at, e.grade
FROM enrollments e
JOIN courses c ON e.course_id = c.id
WHERE e.student_id = 5;

-- All students in a course
SELECT s.name, e.enrolled_at
FROM enrollments e
JOIN students s ON e.student_id = s.id
WHERE e.course_id = 10;

Tip

Add a surrogate auto-increment PK to the junction table when you need to reference it from another table (e.g., a payments table referencing a specific enrollment).

Self-Referential Relationship

A self-referential (or recursive) relationship occurs when a table references itself. This models hierarchical data such as organizational charts, category trees, threaded comments, or bill-of-materials structures.

SQL

-- Category tree: a category can have a parent category
CREATE TABLE categories (
  id        INT          NOT NULL AUTO_INCREMENT,
  name      VARCHAR(100) NOT NULL,
  parent_id INT          DEFAULT NULL,   -- NULL for root categories
  PRIMARY KEY (id),
  CONSTRAINT fk_category_parent
    FOREIGN KEY (parent_id) REFERENCES categories (id) ON DELETE SET NULL
);

-- Electronics -> Phones -> Smartphones (3 levels)
INSERT INTO categories (name, parent_id) VALUES ('Electronics', NULL);   -- id 1
INSERT INTO categories (name, parent_id) VALUES ('Phones', 1);           -- id 2
INSERT INTO categories (name, parent_id) VALUES ('Smartphones', 2);      -- id 3
INSERT INTO categories (name, parent_id) VALUES ('Laptops', 1);          -- id 4

-- Display hierarchy with a self-join (two levels)
SELECT
  child.name   AS category,
  parent.name  AS parent_category
FROM categories child
LEFT JOIN categories parent ON child.parent_id = parent.id;

-- Recursive query for unlimited depth (MySQL 8.0+ CTE)
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT CONCAT(REPEAT('  ', depth), name) AS indented_name, depth
FROM category_tree
ORDER BY depth, name;

Polymorphic Associations Pattern

Polymorphic associations allow a child table to reference rows from multiple different parent tables. A classic example is a comments table that can attach to either a post or a photo or a video.

MySQL does not natively enforce polymorphic FKs. The pattern uses a target_type column alongside a target_id column.

SQL

-- Polymorphic comments: can belong to posts OR photos
CREATE TABLE comments (
  id          INT          NOT NULL AUTO_INCREMENT,
  user_id     INT          NOT NULL,
  target_type VARCHAR(50)  NOT NULL,   -- 'post', 'photo', 'video'
  target_id   INT          NOT NULL,   -- ID in the respective table
  body        TEXT         NOT NULL,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_target (target_type, target_id)   -- index for lookups
);

-- Insert a comment on a post
INSERT INTO comments (user_id, target_type, target_id, body)
VALUES (1, 'post', 42, 'Great article!');

-- Fetch all comments on post 42
SELECT c.body, c.created_at
FROM comments c
WHERE c.target_type = 'post' AND c.target_id = 42;

Warning

Polymorphic associations cannot use database-level foreign key constraints because the target table varies per row. You must enforce referential integrity in application code. Consider using separate junction tables (e.g., post_comments, photo_comments) for strict FK enforcement.

Practical E-Commerce Schema Example

SQL

-- Complete e-commerce schema demonstrating all relationship types

CREATE TABLE users (
  id            INT          NOT NULL AUTO_INCREMENT,
  email         VARCHAR(255) NOT NULL UNIQUE,
  name          VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);

-- 1:1 with users
CREATE TABLE user_payment_info (
  user_id        INT         NOT NULL,
  stripe_cust_id VARCHAR(50),
  PRIMARY KEY (user_id),
  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

-- 1:N with users
CREATE TABLE orders (
  id          INT            NOT NULL AUTO_INCREMENT,
  user_id     INT            NOT NULL,
  status      VARCHAR(20)    NOT NULL DEFAULT 'pending',
  total       DECIMAL(10, 2) NOT NULL DEFAULT 0,
  created_at  DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT
);

CREATE TABLE products (
  id       INT            NOT NULL AUTO_INCREMENT,
  name     VARCHAR(255)   NOT NULL,
  price    DECIMAL(10, 2) NOT NULL,
  stock    INT            NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

-- M:N junction: orders and products
CREATE TABLE order_items (
  order_id   INT            NOT NULL,
  product_id INT            NOT NULL,
  quantity   INT            NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id)   REFERENCES orders   (id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT
);

CREATE TABLE categories (
  id        INT          NOT NULL AUTO_INCREMENT,
  name      VARCHAR(100) NOT NULL,
  parent_id INT          DEFAULT NULL,            -- self-referential 1:N
  PRIMARY KEY (id),
  FOREIGN KEY (parent_id) REFERENCES categories (id) ON DELETE SET NULL
);

-- M:N: products can belong to multiple categories
CREATE TABLE product_categories (
  product_id  INT NOT NULL,
  category_id INT NOT NULL,
  PRIMARY KEY (product_id, category_id),
  FOREIGN KEY (product_id)  REFERENCES products   (id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE CASCADE
);

Quick Reference

Relationship	FK Location	Example
One-to-One	FK + PK on child table	users -> user_profiles
One-to-Many	FK on the "many" (child) table	customers -> orders
Many-to-Many	Junction table with two FKs	students <-> courses via enrollments
Self-referential	FK references own PK	categories.parent_id -> categories.id
Polymorphic	type + id columns (no DB FK)	comments -> posts or photos