Database Relationships

Introduction

Relational databases get their name because tables can be related to one another. Understanding exactly how two tables relate is one of the most important skills in database design — it determines how you write foreign keys, how you JOIN tables, and even whether you need an extra table just to hold the relationship itself.

There are exactly three kinds of relationships you will encounter between any two tables: one-to-one, one-to-many, and many-to-many. This lesson covers all three, with a concrete real-world example for each.

What is a Relationship?

A relationship describes how rows in one table connect to rows in another table, usually through a foreign key. The "shape" of that connection — how many rows on each side can be linked — is what defines the type of relationship.

One-to-One Relationships

In a one-to-one relationship, each row in Table A matches at most one row in Table B, and each row in Table B matches at most one row in Table A. A classic real-world example is a person and their passport — one person has exactly one passport, and one passport belongs to exactly one person.

CREATE TABLE persons (
    person_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE passports (
    passport_id INT PRIMARY KEY,
    person_id INT UNIQUE,
    passport_number VARCHAR(20),
    FOREIGN KEY (person_id) REFERENCES persons(person_id)
);

INSERT INTO persons VALUES (1, 'Priya');
INSERT INTO passports VALUES (1001, 1, 'X1234567');

SELECT p.name, pp.passport_number
FROM persons p
JOIN passports pp ON p.person_id = pp.person_id;

+-------+------------------+
| name  | passport_number  |
+-------+------------------+
| Priya | X1234567         |
+-------+------------------+

One-to-Many Relationships

In a one-to-many relationship, one row in Table A can match many rows in Table B, but each row in Table B matches only one row in Table A. This is the most common relationship in database design. A customer placing many orders is the textbook example — one customer, many orders, but each order belongs to exactly one customer.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_total DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO customers VALUES (1, 'Alex');
INSERT INTO orders VALUES (101, 1, 50.00), (102, 1, 120.00);

SELECT c.name, o.order_id, o.order_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

+------+----------+-------------+
| name | order_id | order_total |
+------+----------+-------------+
| Alex | 101      | 50.00       |
| Alex | 102      | 120.00      |
+------+----------+-------------+

Notice that the foreign key (customer_id) lives on the "many" side of the relationship — the orders table — pointing back to the "one" side, customers.

Many-to-Many Relationships

In a many-to-many relationship, many rows in Table A can match many rows in Table B, in both directions. A student can enroll in many courses, and a course can have many students enrolled. Neither table can hold a simple foreign key to the other, because either side could need to reference multiple rows on the other side at once.

Building a Junction Table

The solution is a junction table (also called a bridge table or a linking table) — a table whose only job is to store pairs of foreign keys, one row per relationship between the two sides.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    title VARCHAR(100)
);

-- Junction table linking students and courses
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

INSERT INTO students VALUES (1, 'Priya'), (2, 'Sam');
INSERT INTO courses VALUES (10, 'Mathematics'), (20, 'History');

-- Priya takes both courses, Sam takes only History
INSERT INTO student_courses VALUES (1, 10), (1, 20), (2, 20);

Now you can find every course a given student takes, or every student enrolled in a given course, by joining through the junction table in either direction.

SELECT s.name, c.title
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
ORDER BY s.name;

+-------+-------------+
| name  | title       |
+-------+-------------+
| Priya | History     |
| Priya | Mathematics |
| Sam   | History     |
+-------+-------------+

Choosing the Right Relationship

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

Every relationship between two tables is one of three shapes: one-to-one, one-to-many, or many-to-many. Recognizing which shape applies tells you exactly where to place your foreign keys, and whether you need a junction table at all.

In this lesson, you learned all three relationship types with real-world examples, and built a student_courses junction table to model a many-to-many relationship. Next, you will learn how to nest one query inside another using subqueries.