Relationships in Databases: One-to-One, One-to-Many, and Many-to-Many

What Are Relationships in Databases?

Relationships describe how data in different tables is connected. They allow you to efficiently retrieve related data while maintaining a clear and logical structure.

• One-to-One Relationship

Each record in Table A is linked to exactly one record in Table B.

Example: A users table and a user_profiles table.

Use Case: Splitting rarely used attributes into a separate table for performance.

Schema:

CREATE TABLE users (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Email VARCHAR(100)
);

CREATE TABLE user_profiles (
    ProfileID INT PRIMARY KEY,
    UserID INT UNIQUE,
    Bio TEXT,
    FOREIGN KEY (UserID) REFERENCES users(ID)
);

• One-to-Many Relationship

One record in Table A can be linked to multiple records in Table B.

Example: A users table and an orders table.

Use Case: A user can place multiple orders.

Schema:

CREATE TABLE orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    UserID INT,
    FOREIGN KEY (UserID) REFERENCES users(ID)
);

• Many-to-Many Relationship

Each record in Table A can be linked to multiple records in Table B, and vice versa.

Example: A students table and a courses table.

Use Case: A student can enroll in multiple courses, and each course can have multiple students.

Schema:

CREATE TABLE students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50)
);

CREATE TABLE courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(50)
);

CREATE TABLE enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    FOREIGN KEY (StudentID) REFERENCES students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES courses(CourseID)
);

Challenge: Design Relationships

Scenario: You’re creating a database for a hospital. Identify the relationships between the following entities: Patients, Doctors, and Appointments.

What types of relationships (one-to-one, one-to-many, or many-to-many) exist between these entities?