🔗 Lesson 11: Table Relationships

Why Multiple Tables?

Imagine storing everything in one giant table:

This works until you notice the problems:

The solution: split the data into separate tables and connect them with relationships.

Now Alice's info is stored once in the customers table. Her orders reference her by ID. Update her email in one place, and it's correct everywhere.

Primary Keys & Foreign Keys

Relationships depend on two key concepts you've already seen:

Primary Key (PK) — The Identifier

Every table should have a primary key — a column (or set of columns) that uniquely identifies each row. You've been using these since Lesson 4:

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,   -- unique identifier
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);
                

Foreign Key (FK) — The Connector

A foreign key is a column in one table that references the primary key of another table. It's the link that creates the relationship:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,              -- foreign key
    order_date DATE,
    total DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);
                

The customer_id column in the orders table stores a value that matches an id in the customers table. This is how MySQL knows which customer placed which order.

One-to-Many (1:M) Relationships

The one-to-many relationship is by far the most common type in relational databases. One record in the parent table can be linked to many records in the child table, but each child record links to exactly one parent.

One customer can place many orders, but each order belongs to exactly one customer.

Real-World Examples

How to Build It

The foreign key always goes on the "many" side:

-- Parent table (the "one" side)
CREATE TABLE authors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    birth_year INT
);

-- Child table (the "many" side) — has the foreign key
CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INT NOT NULL,
    price DECIMAL(6, 2),
    FOREIGN KEY (author_id) REFERENCES authors(id)
);
                

One-to-One (1:1) Relationships

In a one-to-one relationship, each record in Table A is linked to exactly one record in Table B, and vice versa. These are less common and are typically used for:

• Splitting a wide table — separating rarely-accessed columns to improve performance
• Security — keeping sensitive data in a separate, restricted table
• Optional data — information that only some records have

Implementation

A 1:1 relationship uses a foreign key with a UNIQUE constraint — this prevents multiple rows from pointing to the same parent:

CREATE TABLE customer_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNIQUE NOT NULL,   -- UNIQUE enforces 1:1
    bio TEXT,
    avatar_url VARCHAR(255),
    date_of_birth DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);
                

The UNIQUE constraint on customer_id ensures each customer can have at most one profile. Without it, this would be a one-to-many relationship.

Real-World Examples

Many-to-Many (M:M) Relationships

In a many-to-many relationship, records on both sides can link to multiple records on the other side:

• One book can belong to many categories (Fiction, Sci-Fi, Classic)
• One category can contain many books

You can't represent this with a single foreign key — you'd need multiple values in one column, which breaks relational rules. The solution is a junction table (also called a bridge table, linking table, or join table).

The Junction Table Pattern

The junction table book_categories sits between the two main tables. Each row represents one link — "this book is in this category." The two foreign keys together form a composite primary key.

Creating the Tables

-- Table A
CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    price DECIMAL(6, 2)
);

-- Table B
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- Junction table (the bridge)
CREATE TABLE book_categories (
    book_id INT NOT NULL,
    category_id INT NOT NULL,
    PRIMARY KEY (book_id, category_id),    -- composite PK
    FOREIGN KEY (book_id) REFERENCES books(id),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);
                

Inserting Data

-- Add some categories
INSERT INTO categories (name) VALUES
    ('Fiction'), ('Science Fiction'), ('Classic'), ('Dystopian');

-- Link books to categories (a book can have multiple categories)
INSERT INTO book_categories (book_id, category_id) VALUES
    (1, 1),    -- Dune → Fiction
    (1, 2),    -- Dune → Science Fiction
    (2, 1),    -- 1984 → Fiction
    (2, 4),    -- 1984 → Dystopian
    (2, 3),    -- 1984 → Classic
    (3, 1),    -- Pride and Prejudice → Fiction
    (3, 3);    -- Pride and Prejudice → Classic
                

Real-World Examples

Junction Tables with Extra Data

Junction tables often carry additional data about the relationship itself:

-- Order items — the junction between orders and books
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    book_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,       -- extra data!
    price_at_time DECIMAL(6, 2) NOT NULL,  -- extra data!
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);
                

The quantity and price_at_time columns belong to neither the order nor the book — they describe the relationship between a specific order and a specific book.

Reading ER Diagrams

An Entity-Relationship (ER) diagram is a visual blueprint of your database. You've been seeing them throughout this lesson. Here's how to read the notation:

Crow's Foot Notation

The most common ER diagram style uses symbols at the ends of relationship lines:

Reading this diagram:

• A customer places zero or many orders (a new customer may have no orders yet)
• An order contains one or many order items (an order must have at least one item)
• A book appears in zero or many order items (some books may never be ordered)
• A book belongs to zero or many categories, and a category contains zero or many books (many-to-many)

Building a Bookstore Schema

Let's put it all together and design a complete schema for our bookstore. This is what we'll use for the rest of the course:

The Complete SQL

-- 1. Authors (no dependencies)
CREATE TABLE authors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    birth_year INT
);

-- 2. Books (depends on authors)
CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INT NOT NULL,
    price DECIMAL(6, 2) NOT NULL,
    pages INT,
    published DATE,
    in_stock BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

-- 3. Categories (no dependencies)
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- 4. Book-Category junction (depends on books, categories)
CREATE TABLE book_categories (
    book_id INT NOT NULL,
    category_id INT NOT NULL,
    PRIMARY KEY (book_id, category_id),
    FOREIGN KEY (book_id) REFERENCES books(id),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- 5. Customers (no dependencies)
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    city VARCHAR(50)
);

-- 6. Orders (depends on customers)
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10, 2) DEFAULT 0,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- 7. Order items (depends on orders, books)
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    book_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    price_at_time DECIMAL(6, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);
                

Common Mistakes

Mistake 1: Putting the FK on the Wrong Side

-- ⛔ WRONG — FK on the "one" side
-- This says each customer can only have ONE order!
ALTER TABLE customers ADD COLUMN order_id INT;

-- ✅ CORRECT — FK on the "many" side
-- This allows each customer to have MANY orders
ALTER TABLE orders ADD COLUMN customer_id INT;
                

The foreign key always goes on the "many" side of a 1:M relationship.

Mistake 2: Storing Comma-Separated Values

-- ⛔ WRONG — storing multiple categories in one column
INSERT INTO books (title, categories) VALUES
    ('Dune', 'Fiction,Sci-Fi,Classic');

-- ✅ CORRECT — use a junction table
INSERT INTO book_categories VALUES (1, 1), (1, 2), (1, 3);
                

Never store multiple values in a single column. It makes querying, updating, and maintaining data integrity nearly impossible.

Mistake 3: Forgetting the UNIQUE Constraint for 1:1

-- ⛔ WRONG — this is actually 1:M without UNIQUE
CREATE TABLE profiles (
    id INT PRIMARY KEY,
    customer_id INT,   -- missing UNIQUE!
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- ✅ CORRECT — UNIQUE enforces 1:1
CREATE TABLE profiles (
    id INT PRIMARY KEY,
    customer_id INT UNIQUE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);
                

Mistake 4: Creating Tables in Wrong Order

-- ⛔ WRONG — books references authors, but authors doesn't exist yet
CREATE TABLE books (
    id INT PRIMARY KEY,
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(id)  -- ERROR!
);
CREATE TABLE authors (id INT PRIMARY KEY, name VARCHAR(100));

-- ✅ CORRECT — create parent first
CREATE TABLE authors (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE books (
    id INT PRIMARY KEY,
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);
                

Exercises

CREATE TABLE artists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    country VARCHAR(50)
);

CREATE TABLE albums (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    artist_id INT NOT NULL,
    release_year INT,
    FOREIGN KEY (artist_id) REFERENCES artists(id)
);

CREATE TABLE songs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    album_id INT NOT NULL,
    duration_seconds INT,
    FOREIGN KEY (album_id) REFERENCES albums(id)
);

CREATE TABLE playlists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_date DATE NOT NULL
);

CREATE TABLE playlist_songs (
    playlist_id INT NOT NULL,
    song_id INT NOT NULL,
    position INT,   -- track order in the playlist
    PRIMARY KEY (playlist_id, song_id),
    FOREIGN KEY (playlist_id) REFERENCES playlists(id),
    FOREIGN KEY (song_id) REFERENCES songs(id)
);
                        

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    course_name VARCHAR(100)   -- "Math, Science, English"
);
                    

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    employee_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);
                    

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);
                    

Summary

Quick Reference

📚 Additional Resources

• MySQL Docs — Foreign Key Constraints
• MySQL Docs — AUTO_INCREMENT
• MySQL Docs — Primary Key Constraints

🚀 What's Next?

Now that you understand how tables relate to each other, it's time to make those relationships official and enforced. In the next lesson, you'll learn about foreign key constraints — the rules that MySQL enforces to keep your relationships consistent. What happens when you delete a customer who has orders? What if you try to create an order for a non-existent customer? Foreign keys answer these questions with ON DELETE and ON UPDATE rules.