Types of Relationships

Introduction

Tables contain primary and foreign keys to specify relationships.

• Primary keys:
  • The column(s) that identify the unique objects in a table.
• Foreign keys:
  • The column that links to a primary key in another table.

There are different types of relationships:

1. One-to-one
2. One-to-many
3. Many-to-many

In this module, we will learn about these relationships and their differences.

---

1. One-to-one relationship

In a one-to-one relationship, 1 record from one table is linked to 0 or 1 record from another table.

Let's look at the following example:

Here we see the following:

• Table students contains the first name and last name of students.
  • Each row contains data of a unique student.
  • Primary key is column student_id.
• Table student_details contains details of a student: email and date of birth.
  • Each row contains details of a unique student.
  • Primary key is column student_id.

Here, 1 record from table students is linked to 0 or 1 record from table student_details.

This is based on matching columns student_id.

The one-to-one relationship occurs, for example, when many columns are needed to process all the details of an object.

For better overview, the data is then distributed over multiple tables.

---

2. One-to-many relationship

In a one-to-many relationship, 1 record from one table is linked to 0 or multiple records from another table.

Let's look at the following example:

Here we see the following:

• Table students contains the first name and last name of students.
  • Each row contains data of a unique student.
  • Primary key is column student_id.
• Table course_subscriptions contains the courses for which a student is enrolled.
  • Each student can be enrolled in multiple courses.
  • A foreign key is column student_id.

Here, 1 record from table students is linked to 0 or multiple records from table course_subscriptions.

This is based on matching columns student_id.

The one-to-many relationship is the most common in practice.

There is generally a relationship between a primary key and a foreign key.

---

3. Many-to-many relationship

In a many-to-many relationship, 1 record from one table is linked to 0 or multiple records from another table, and vice versa.

Let's look at the following example:

Here we see the following:

• Table students contains the first name and last name of students.
  • Each row contains data of a unique student.
  • Primary key is column student_id.
• Table course_subscriptions contains the courses for which a student is enrolled.
  • Each student can be enrolled in multiple courses.
  • A foreign key is column student_id.
  • A foreign key is column course_id.
• Table courses contains details of the courses.
  • Each row contains data of a unique course.
  • Primary key is column course_id.

Each student can be enrolled in multiple courses. Each course can be taken by multiple students.

There is generally a relationship between a primary key in one table, via foreign keys in an intermediate table, with a primary key in another table.

---

Summary

• There are different types of relationships:
  1. One-to-one
     • 1 record from one table is linked to 0 or 1 record from another table.
  2. One-to-many
     • 1 record from one table is linked to 0 or multiple records from another table.
  3. Many-to-many
     • 1 record from one table is linked to 0 or multiple records from another table, and vice versa.
• The one-to-many relationship is the most common in practice.