This content originally appeared on DEV Community and was authored by Bellamer
What Are Relationships in Databases?
Relationships describe how data in different tables is connected. They allow you to efficiently retrieve related data while maintaining a clear and logical structure.
- One-to-One Relationship
Each record in Table A is linked to exactly one record in Table B.
Example: A users table and a user_profiles table.
Use Case: Splitting rarely used attributes into a separate table for performance.
Schema:
CREATE TABLE users (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Email VARCHAR(100)
);
CREATE TABLE user_profiles (
ProfileID INT PRIMARY KEY,
UserID INT UNIQUE,
Bio TEXT,
FOREIGN KEY (UserID) REFERENCES users(ID)
);
- One-to-Many Relationship
One record in Table A can be linked to multiple records in Table B.
Example: A users table and an orders table.
Use Case: A user can place multiple orders.
Schema:
CREATE TABLE orders (
OrderID INT PRIMARY KEY,
OrderDate DATE,
UserID INT,
FOREIGN KEY (UserID) REFERENCES users(ID)
);
- Many-to-Many Relationship
Each record in Table A can be linked to multiple records in Table B, and vice versa.
Example: A students table and a courses table.
Use Case: A student can enroll in multiple courses, and each course can have multiple students.
Schema:
CREATE TABLE students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50)
);
CREATE TABLE courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(50)
);
CREATE TABLE enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
FOREIGN KEY (StudentID) REFERENCES students(StudentID),
FOREIGN KEY (CourseID) REFERENCES courses(CourseID)
);
Challenge: Design Relationships
Scenario: You’re creating a database for a hospital. Identify the relationships between the following entities: Patients, Doctors, and Appointments.
What types of relationships (one-to-one, one-to-many, or many-to-many) exist between these entities?
This content originally appeared on DEV Community and was authored by Bellamer
Bellamer | Sciencx (2025-04-13T22:54:00+00:00) Relationships in Databases: One-to-One, One-to-Many, and Many-to-Many. Retrieved from https://www.scien.cx/2025/04/13/relationships-in-databases-one-to-one-one-to-many-and-many-to-many/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.