How Do One-to-One, One-to-Many, and Many-to-Many Database Relationships Work?

Mastering Database Relationships for Efficient Data Management

Effective database design hinges on properly structuring tables and defining relationships between them. Three fundamental relationship types govern how records connect: one-to-one, one-to-many, and many-to-many. Let's explore each type and their implementation.

One-to-One Relationships

A one-to-one relationship signifies a one-to-one correspondence between records in two tables. This is achieved using a foreign key in the dependent table referencing the primary key of the parent table.

Example:

-- Table 1: Student
CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    address_id INT
);

-- Table 2: Address
CREATE TABLE Address (
    address_id INT PRIMARY KEY,
    address VARCHAR(255),
    city VARCHAR(255),
    zipcode VARCHAR(10),
    student_id INT UNIQUE
);

Each student_id uniquely maps to an address_id, illustrating a one-to-one link. Note the UNIQUE constraint on student_id in the Address table.

One-to-Many Relationships

In a one-to-many relationship, a single record in one table can relate to multiple records in another. The dependent table uses a foreign key referencing the parent table's primary key.

Example:

-- Table 1: Teacher
CREATE TABLE Teacher (
    teacher_id INT PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255)
);

-- Table 2: Class
CREATE TABLE Class (
    class_id INT PRIMARY KEY,
    class_name VARCHAR(255),
    teacher_id INT
);

One teacher can teach multiple classes, but each class has only one teacher.

Many-to-Many Relationships

A many-to-many relationship connects multiple records in one table to multiple records in another. This necessitates a junction table containing foreign keys from both tables.

Example:

-- Table 1: Student
CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255)
);

-- Table 2: Class
CREATE TABLE Class (
    class_id INT PRIMARY KEY,
    name VARCHAR(255)
);

-- Junction Table: Student_Class
CREATE TABLE Student_Class (
    class_id INT,
    student_id INT,
    PRIMARY KEY (class_id, student_id)
);

The Student_Class table allows for multiple students in multiple classes.

Data Integrity: Enforcing UNIQUE constraints on foreign keys in one-to-one and one-to-many relationships is crucial for maintaining data consistency.

Illustrative Queries:

-- Students in a specific class:
SELECT s.student_id, s.last_name
FROM Student_Class sc
JOIN Student s ON s.student_id = sc.student_id
WHERE sc.class_id = 1;

-- Classes attended by a specific student:
SELECT c.class_id, c.name
FROM Student_Class sc
JOIN Class c ON c.class_id = sc.class_id
WHERE sc.student_id = 2;

The above is the detailed content of How Do One-to-One, One-to-Many, and Many-to-Many Database Relationships Work?. For more information, please follow other related articles on the PHP Chinese website!