Have you ever wondered how apps like university portals know which courses a student is enrolled in, or how they pull up an instructor's full schedule in seconds?
The answer lies in database relationships - one of the most important concepts in backend development.
In this article, we'll explore:
- What database relationships are and why they matter
- The three types of relationships: One-to-One, One-to-Many, and Many-to-Many
- How relationship schemas work (primary keys, foreign keys)
- How SQL Joins let you pull connected data from multiple tables
To keep things grounded, we'll use one running example throughout: a University Management System.
By the end, you won't just understand the theory, you'll see exactly how these concepts connect in a real-world scenario.
What Are Database Relationships?
A database relationship defines how data in one table connects to data in another.
Instead of storing the same information repeatedly, relational databases organize data into separate tables and link them using keys.
Think about our university system. We have a table for students and another for courses.
A student can enroll in multiple courses, and each course can have many students. Rather than storing a student's full details on every course record, we store the student's info once and create a relationship between the two tables.
This keeps data clean, reduces duplication, and makes updates easy. If a student's email changes? Update it in one place - done.
Here's a simple visual of what that looks like:
+------------------+ +------------------+
| Students | | Courses |
+------------------+ +------------------+
| student_id (PK) | | course_id (PK) |
| name | | title |
| email | | credits |
+------------------+ +------------------+
\ /
\ /
\ /
Enrollments (links students ↔ courses)
Now let's look at the three types of relationships you'll encounter.
Types of Database Relationships
1. One-to-One (1:1)
Each record in Table A matches exactly one record in Table B and vice versa.
University example: Every student has one student profile (bio, photo, address).
-- Students table
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
-- Profiles table
CREATE TABLE profiles (
profile_id INT PRIMARY KEY,
student_id INT UNIQUE,
bio TEXT,
photo_url VARCHAR(255),
FOREIGN KEY (student_id) REFERENCES students(student_id)
);
The UNIQUE constraint on student_id in the profiles table is what enforces the one-to-one rule; no two profiles can belong to the same student.
2. One-to-Many (1:N)
One record in Table A can relate to many records in Table B.
University example: One department has many students. One instructor teaches many courses.
-- Departments table
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(100)
);
-- Students table (updated)
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
Many students share the same dept_id. That's your one-to-many relationship.
3. Many-to-Many (M:N)
Records in Table A can relate to many records in Table B, and records in Table B can relate to many in Table A.
University example: Students enroll in many courses. Each course has many students enrolled.
You can't link these directly but instead you need a junction table (also called a bridge or pivot table).
-- Enrollments (junction table)
CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT,
course_id INT,
enrolled_date DATE,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
Understanding Relationship Schemas
Primary Keys
A Primary Key (PK) uniquely identifies every row in a table. No two rows can share the same value.
In our students table, student_id is the primary key therefore every student gets a unique ID.
Foreign Keys
A Foreign Key (FK) is a column in one table that points to the primary key of another table.
In enrollments, both student_id and course_id are foreign keys. They reference the students and courses tables respectively.
Why This Matters
Keys are what make relationships real in a database. Without them, your tables are just isolated spreadsheets. With them, your database understands how data connects, and your queries can take advantage of that.
SQL Joins
Now that our tables are related, Joins are how we query across them.
Let's use these sample tables:
students enrollments courses
----------- ----------- -----------
1 | Alice 1 | 1 | 101 101 | Math
2 | Bob 2 | 1 | 102 102 | Physics
3 | Carol 3 | 2 | 101 103 | History
INNER JOIN
Returns only rows where there's a match in both tables.
-- Which courses is Alice enrolled in?
SELECT students.name, courses.title
FROM students
INNER JOIN enrollments ON students.student_id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.course_id
WHERE students.name = 'Alice';
Result: Carol appears with NULL for course title — she exists but isn't enrolled yet.
RIGHT JOIN
The opposite of LEFT JOIN, it returns all rows from the right table, plus matches from the left.
-- Show all courses, even ones with no students enrolled
SELECT students.name, courses.title
FROM students
RIGHT JOIN enrollments ON students.student_id = enrollments.student_id
RIGHT JOIN courses ON enrollments.course_id = courses.course_id;
Result: History (103) shows up with NULL for student name where the course exists but nobody enrolled.
FULL OUTER JOIN
Returns everything from both tables. NULLs fill in wherever there's no match.
SELECT students.name, courses.title
FROM students
FULL OUTER JOIN enrollments ON students.student_id = enrollments.student_id
FULL OUTER JOIN courses ON enrollments.course_id = courses.course_id;
Result: Every student and every course appears whether matched or not.
Conclusion
Let's recap what we covered:
- Database relationships prevent data duplication by linking tables instead of repeating data
- One-to-One: one student, one profile
- One-to-Many: one department, many students
- Many-to-Many: students ↔ courses through an enrollments table
- Primary Keys uniquely identify rows; Foreign Keys point to another table's PK
- SQL Joins let you query across related tables ,INNER, LEFT, RIGHT, and FULL OUTER
Our university management system went from isolated tables to a fully connected, queryable database.
That's the power of relational databases.
If this helped, drop a comment or reaction below and let me know which SQL join trips you up the most!
Top comments (0)