SQL Training: Relationships

1. One-to-One Relationship

A One-to-One relationship exists between two tables when a single record in the first table is associated with exactly one record in the second table, and vice versa. This type of relationship is less common but can be useful for splitting data into separate tables for security, performance, or organizational reasons.

Example:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE CustomerDetails (
    CustomerID INT PRIMARY KEY,
    Address VARCHAR(255),
    Phone VARCHAR(20),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

In this example, each customer (in the Customers table) has exactly one set of details (in the CustomerDetails table).

2. One-to-Many Relationship

A One-to-Many relationship exists between two tables when a single record in the first table can be associated with one or more records in the second table, but each record in the second table is associated with only one record in the first table. This is the most common type of relationship in databases.

Example:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100)
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

In this example, each department (in the Departments table) can have many employees (in the Employees table), but each employee belongs to only one department.

3. Many-to-Many Relationship

A Many-to-Many relationship exists between two tables when a single record in the first table can be associated with one or more records in the second table, and vice versa. This type of relationship requires a junction table to manage the associations between the two tables.

Example:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100)
);

CREATE TABLE Enrollments (
    StudentID INT,
    CourseID INT,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

In this example, each student (in the Students table) can enroll in many courses (in the Courses table), and each course can have many students. The Enrollments table acts as a junction table to manage these many-to-many relationships.