Kun je de soorten relaties tussen tabellen in een database opsommen?

In databases, there are three main types of relationships between tables:

1. One-to-One — each record in the first table corresponds to exactly one record in the second table and vice versa. Used when data is logically separated but directly related.

2. One-to-Many — one record in the first table corresponds to many records in the second. For example, one user can have many orders.

3. Many-to-Many — records in the first table can correspond to many records in the second and vice versa. Implemented through an intermediate table containing foreign keys of both tables.

Example of a "many-to-many" relationship:

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

-- Courses table
CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    Title VARCHAR(100)
);

-- Intermediate table for many-to-many relationship
CREATE TABLE StudentCourses (
    StudentID INT,
    CourseID INT,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

These relationship types are fundamental in relational data modeling.