What are the types of relationships between tables?

There are three main types of relationships between tables in relational databases:

1. One-to-one (1:1) — 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 (1:N) — one record in the first table corresponds to many records in the second. For example, one customer can have many orders.

3. Many-to-many (M:N) — records in the first table are related to many records in the second and vice versa. Usually, an intermediate linking table with foreign keys to both tables is created.

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)
);

-- Linking table for students and courses
CREATE TABLE StudentCourses (
  StudentID INT,
  CourseID INT,
  PRIMARY KEY (StudentID, CourseID),
  FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
  FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);