Types of Database Relationships

One-to-One (1:1) Relationship

• Definition: A single record in one table is related to only one record in another table, and vice versa.

• Example: A User table and a UserProfile table, where each user has only one profile.

• Use Case: Used when I want to split a table for security, performance, or organizational reasons.

One-to-One Code Example
Add a foreign key to one of the tables that references the primary key of the other table.

CREATE TABLE User (
user_id INT PRIMARY KEY,
username VARCHAR(50)
);

CREATE TABLE UserProfile (
profile_id INT PRIMARY KEY,
user_id INT UNIQUE,
bio TEXT,
FOREIGN KEY (user_id) REFERENCES User(user_id)
);

One-to-Many (1:N ) Relationship

• Definition: A single record in one table can be related to one or more records in another table.

• Example: A Customer table and an Orders table, where one customer can place many orders.

• Use Case: Most common relationship type, used in scenarios like blog posts and comments, or authors and books.

One to Maney Code Example

Add a foreign key to the "many" side table that references the primary key of the "one" side table.

CREATE TABLE Customer (
customer_id INT PRIMARY KEY,
name VARCHAR(100)
);

CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);

Many-to-Many (M:N ) Relationship

• Definition: Multiple records in one table are related to multiple records in another table.

• Example: A Students table and a Courses table, where many students can enroll in many courses.

• Implementation: Requires a junction table (also called an associative entity or bridge table) to link the two tables.

Many-to-Many Code Example

Create a junction table with foreign keys referencing the primary keys of both tables.

CREATE TABLE Students (
student_id INT PRIMARY KEY,
name VARCHAR(100)
);

CREATE TABLE Courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100)
);

CREATE TABLE StudentCourses (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students(student_id),
FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);