Database Relationships: One-to-One, One-to-Many and Many-to-Many

Remy Sterling

15 min read

·

Sep 14, 2026

--

Listen

Share

Press enter or click to view image in full size

Introduction

A student can enroll in multiple courses, a course can have many students, and each student may have a single university ID. These connections are not coincidental — they represent fundamental database relationships that form the backbone of every well-designed relational database.

In database management systems (DBMS), relationships define how data in one table connects to data in another. Understanding these connections is essential for anyone working with databases, whether you’re designing a simple application or building complex enterprise systems.

There are three primary types of database relationships:

• One-to-One (1:1) — Each record in one table relates to exactly one record in another table
• One-to-Many (1:N) — One record in a table relates to multiple records in another table
• Many-to-Many (M:N) — Multiple records in one table relate to multiple records in another table

Mastering these relationship types is crucial for database design, creating accurate ER diagrams, writing effective SQL queries, and completing DBMS assignments successfully. This guide breaks down each relationship type with clear examples, SQL implementations, and common pitfalls to avoid.

What Is a Database Relationship?

A database relationship describes how records in one table are associated with records in another table. In relational databases, these associations are established through keys, primarily primary keys and foreign keys.

To understand relationships, you first need to understand the basic components:

• Tables — Structures that store data in rows and columns
• Records (Rows) — Individual entries in a table
• Primary Key — A column that uniquely identifies each record in a table
• Foreign Key — A column that references the primary key of another table
• Entities — Real-world objects or concepts represented as tables
• Cardinality — The numerical relationship between entities (1:1, 1:N, M:N)

Simple Example:

Consider two tables — Students and Courses:

Press enter or click to view image in full size

When Rahul enrolls in Database Systems, we need a way to connect these records. That connection is a relationship. The relationship between students and courses tells us which students are enrolled in which courses, and this is where understanding relationship types becomes essential.

If you’re stuck identifying 1:1, 1:N, or M:N relationships for an ER diagram, database schema, or SQL task, AssignmentDude gives you one place to ask for help. You can get guidance with DBMS assignments, database design, ER diagrams, normalization, and SQL queries, so you can fix relationship mistakes before submission and submit with more confidence.

What Are the Different Types of Database Relationships?

Before diving into each type, here’s a quick comparison:

Press enter or click to view image in full size

Each relationship type serves different data modeling needs. The choice depends entirely on the real-world scenario you’re representing.

One-to-One Relationship

What Is a One-to-One Relationship?

In a one-to-one relationship, each record in Table A is associated with at most one record in Table B, and vice versa. This is the simplest relationship type, though it’s often misunderstood or overused.

Classic Example: Person → Passport

• One person has one passport record
• One passport belongs to one person

This relationship makes sense when certain data should be separated for security, organizational, or performance reasons.

Structure of a One-to-One Relationship

PERSON
person_id (PK)
name
        |
        | 1
        |
        | 1
        |
PASSPORT
passport_id (PK)
person_id (FK, UNIQUE)
passport_number

Notice that the foreign key (person_id) in the Passport table must have a UNIQUE constraint. This ensures that no two passports can belong to the same person—preserving the one-to-one nature of the relationship.

How a One-to-One Relationship Works

The relationship is enforced through:

1. Primary Key — Uniquely identifies each record in both tables
2. Foreign Key — Creates the link between the two tables
3. UNIQUE Constraint — Ensures the foreign key values are not duplicated
4. Referential Integrity — Prevents orphan records by ensuring foreign key values exist in the parent table

One-to-One Relationship Example in SQL

CREATE TABLE Person (
    person_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE Passport (
    passport_id INT PRIMARY KEY,
    person_id INT UNIQUE,
    passport_number VARCHAR(20) NOT NULL,
    FOREIGN KEY (person_id) REFERENCES Person(person_id)
);

What each part does:

• person_id INT PRIMARY KEY – Uniquely identifies each person
• person_id INT UNIQUE – Ensures each person can have only one passport
• FOREIGN KEY (person_id) REFERENCES Person(person_id) – Links passport to a valid person
• ON DELETE CASCADE (optional) – Automatically deletes passport if the person is deleted

When Should You Use a One-to-One Relationship?

One-to-one relationships are appropriate when:

• Employee → Employee Profile — Sensitive HR data stored separately
• Person → Passport — Government identification records
• User → User Settings — Preferences stored in a separate table
• Customer → Account Profile — Extended profile information

These scenarios typically involve data that:

• Has different security requirements
• Is accessed less frequently
• Would make the main table too wide with many NULL columns

Common One-to-One Mistakes

1. Unnecessary one-to-one tables — Splitting data that logically belongs together
2. Missing UNIQUE constraint — Allowing multiple records when only one should exist
3. Incorrect foreign key placement — Putting the FK in the wrong table
4. Using one-to-one when one-to-many is needed — Misunderstanding the actual requirements

One-to-Many Relationship

What Is a One-to-Many Relationship?

In a one-to-many relationship, one record in Table A can relate to multiple records in Table B, while each record in Table B relates to only one record in Table A.

This is the most common relationship type in relational databases. Most real-world data naturally follows this pattern.

Classic Example: Department → Employees

• One department can have many employees
• Each employee belongs to one department

Structure of a One-to-Many Relationship

DEPARTMENT
department_id (PK)
department_name
        |
        | 1
        |
        | N
        |
EMPLOYEE
employee_id (PK)
employee_name
department_id (FK)

The foreign key always sits on the “many” side of the relationship. In this case, department_id appears in the Employee table because each employee belongs to one department.

How One-to-Many Relationships Work

The key principle: The foreign key always goes in the table on the “many” side.

Why? Because each employee needs to store which department they belong to. If we put the foreign key in the Department table, we would need multiple columns to store multiple employee IDs — which violates database normalization principles.

This is a critical concept for beginners to understand. The “many” side holds the reference to the “one” side.

One-to-Many Relationship Example in SQL

CREATE TABLE Department (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE Employee (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Department(department_id)
);

Explanation:

• The Department table stores department information
• The Employee table includes a department_id foreign key
• Each employee record points to exactly one department
• A department can be referenced by multiple employee records

Real-World Examples of One-to-Many Relationships

• Customer → Orders — One customer places many orders
• Department → Employees — One department has many employees
• Author → Books — One author writes many books
• Category → Products — One category contains many products
• Teacher → Students — One teacher teaches many students (in a simple model)

Common One-to-Many Mistakes

1. Putting the foreign key in the wrong table — FK should be on the “many” side
2. Allowing orphan records — Records that reference non-existent parent records
3. Misunderstanding parent/child relationships — Confusing which table is which
4. Using duplicate data instead of a foreign key — Repeating department names instead of referencing department_id

Many-to-Many Relationship

What Is a Many-to-Many Relationship?

In a many-to-many relationship, multiple records in Table A can relate to multiple records in Table B, and vice versa.

Classic Example: Students ↔ Courses

• A student can take multiple courses
• A course can have multiple students

This creates a problem: neither table can hold a foreign key to the other without violating normalization rules. Storing multiple course IDs in a single student record would require comma-separated values — a major design flaw.

The Solution: Junction Tables

Many-to-many relationships require a third table, called a junction table (also known as a bridge table or associative table). This table sits between the two main tables and holds foreign keys to both.

STUDENT
student_id (PK)
name
        |
        | N
        |
   ENROLLMENT
   student_id (FK)
   course_id (FK)
        |
        | N
        |
COURSE
course_id (PK)
course_name

What Is a Junction Table?

A junction table:

• Contains foreign keys referencing both related tables
• Often has a composite primary key made from those foreign keys
• May include additional attributes specific to the relationship (like enrollment date)

Terminology note: Different textbooks and instructors may use different terms:

• Junction table
• Bridge table
• Associative table
• Linking table
• Cross-reference table

All refer to the same concept.

Many-to-Many Relationship Example in SQL

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE Course (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE Enrollment (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Student(student_id),
    FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

Key points:

• The Enrollment table is the junction table
• PRIMARY KEY (student_id, course_id) creates a composite key
• This prevents duplicate enrollments (same student in same course twice)
• Additional attributes like enrollment_date can be stored here

Real-World Examples of Many-to-Many Relationships

• Students ↔ Courses — Through Enrollment
• Products ↔ Orders — Through Order_Items
• Actors ↔ Movies — Through Cast
• Doctors ↔ Patients — Through Appointments
• Authors ↔ Books — Through Authorship (if co-authored)

Common Many-to-Many Mistakes

1. Storing comma-separated IDs — Violates first normal form
2. Adding multiple columns — Like course1, course2, course3 in Student table
3. Ignoring the junction table — Trying to force a direct relationship
4. Creating duplicate relationships — Not enforcing composite primary keys
5. Failing to define appropriate keys — Missing primary or foreign keys

One-to-One vs One-to-Many vs Many-to-Many

Understanding the differences between relationship types is essential for database design decisions.

One-to-One (1:1)

Each record in Table A relates to exactly one record in Table B, and vice versa. Used when data is logically separated but shares a one-to-one correspondence.

Cardinality: 1 : 1

One-to-Many (1:N)

One record in Table A relates to multiple records in Table B, but each record in Table B relates to only one record in Table A.

Cardinality: 1 : N

Many-to-Many (M:N)

Multiple records in Table A relate to multiple records in Table B. Requires a junction table.

Cardinality: M : N

How Cardinality Affects Database Design

Cardinality determines:

• Where foreign keys are placed
• Whether a junction table is needed
• How queries must be structured
• What constraints are required

The difference between one-to-many and many-to-many is particularly important. In a one-to-many relationship, one foreign key in the “many” table suffices. In a many-to-many relationship, a separate junction table is mandatory.

Database Relationships and ER Diagrams

How Are Database Relationships Shown in an ER Diagram?

Entity-Relationship (ER) diagrams provide a visual representation of database structure, including:

• Entities — Rectangles representing tables
• Attributes — Ovals representing columns
• Relationships — Diamonds or lines connecting entities
• Cardinality — Symbols showing relationship type (1, N, M)

Representing Each Relationship Type

One-to-One (1:1)

Person —1————1— Passport

One-to-Many (1:N)

Department —1————N— Employee

Many-to-Many (M:N)

Student —M————N— Course
         |
    (Enrollment)

In proper ER diagrams:

• Crow’s foot notation uses different symbols for “one” and “many”
• Chen notation uses numbers and letters (1, N, M)
• UML notation uses specific multiplicities

Why ER Diagrams Matter

ER diagrams are essential for:

• Planning database structure before implementation
• Communicating design decisions with stakeholders
• Completing ER diagram assignments in DBMS courses
• Documenting existing database schemas

A well-designed ER diagram makes the transition to actual database tables straightforward, you can convert an ER diagram into relational tables systematically.

Database Relationships and Primary/Foreign Keys

How Do Primary Keys and Foreign Keys Create Relationships?

Relationships between tables are implemented through keys:

Primary Key:

• Uniquely identifies each record in a table
• Cannot contain NULL values
• Must be unique across all records

Foreign Key:

• References the primary key of another table
• Creates the actual link between tables
• Can contain NULL values (for optional relationships)
• Enforces referential integrity

The Relationship Chain

Primary Key → Foreign Key → Relationship

Every relationship follows this pattern:

1. Parent table has a primary key
2. Child table has a foreign key referencing that primary key
3. The foreign key constraint enforces the relationship

Referential Integrity

Referential integrity ensures that:

• Foreign key values must exist in the referenced table
• You cannot delete a parent record if child records reference it (unless CASCADE is specified)
• Data remains consistent across related tables

Parent and Child Tables

• Parent table — Contains the primary key being referenced
• Child table — Contains the foreign key doing the referencing

In a one-to-many relationship, the “one” side is the parent and the “many” side is the child.

Database Relationships in SQL

How Are Database Relationships Implemented in SQL?

SQL provides several mechanisms for implementing and managing relationships:

Key SQL Elements:

• PRIMARY KEY – Defines unique identifier
• FOREIGN KEY – Creates relationship to another table
• UNIQUE – Ensures one-to-one relationships
• NOT NULL – Makes relationships mandatory
• REFERENCES – Specifies the parent table
• JOIN – Retrieves data across related tables

Connecting Relationships to Queries

Once relationships are established, SQL JOIN operations allow you to retrieve related data:

Student
   ↓
Enrollment
   ↓
Course

A query to find all courses for a student:

SELECT s.name, c.course_name
FROM Student s
JOIN Enrollment e ON s.student_id = e.student_id
JOIN Course c ON e.course_id = c.course_id
WHERE s.student_id = 101;

Understanding SQL JOINs is directly connected to understanding relationships — you can only join tables that have a defined relationship.

Common Database Relationship Mistakes

Avoiding these mistakes will save hours of debugging and redesign:

Mistake 1: Using the Wrong Relationship Type
Misidentifying whether data requires 1:1, 1:N, or M:N leads to structural problems.

Mistake 2: Putting the Foreign Key in the Wrong Table
In one-to-many relationships, the FK must be on the “many” side.

Mistake 3: Ignoring Cardinality
Failing to analyze how many records can relate prevents proper design.

Mistake 4: Using Repeated Columns for Many-to-Many Data
Columns like course1, course2, course3 violate normalization.

Mistake 5: Storing Multiple IDs in One Field
Comma-separated values in a single column break relational principles.

Mistake 6: Not Enforcing Referential Integrity
Without foreign key constraints, orphan records accumulate.

Mistake 7: Creating Unnecessary Relationships
Not every table needs to connect to every other table.

Mistake 8: ER Diagram Doesn’t Match Actual Database
Documentation and implementation must stay synchronized.

Mistake 9: Using Duplicate Data Instead of Relationships
Repeating data instead of referencing it wastes space and causes inconsistencies.

Mistake 10: Failing to Test with Realistic Data
Relationships should be validated with actual use cases before deployment.

These mistakes frequently appear in common DBMS assignment mistakes, learning to avoid them improves both grades and practical skills.

How to Choose the Right Database Relationship

Use this simple diagnostic process:

Question 1: Can one A have more than one B?

Question 2: Can one B have more than one A?

Then apply this logic:

Press enter or click to view image in full size

Example:

• Can one person have more than one passport? No.
• Can one passport belong to more than one person? No.
• Result: One-to-One relationship
• Can one department have more than one employee? Yes.
• Can one employee belong to more than one department? No.
• Result: One-to-Many relationship
• Can one student take more than one course? Yes.
• Can one course have more than one student? Yes.
• Result: Many-to-Many relationship

This simple framework helps beginners quickly identify the correct relationship type.

Database Relationship Examples in Real-World Systems

University Database

Student → Enrollment → Course

Many-to-many relationship. Students enroll in multiple courses; courses have multiple students. The Enrollment junction table also stores enrollment date, grade, and semester.

E-commerce Database

Customer → Order

One-to-many relationship. One customer places many orders; each order belongs to one customer.

Order → Order_Items → Product

Many-to-many relationship. Orders contain multiple products; products appear in multiple orders.

Company Database

Department → Employee

One-to-many relationship. One department has many employees; each employee belongs to one department.

Identity System

Person → Passport

One-to-one relationship. Each person has one passport; each passport belongs to one person.

Content Platform

Author ↔ Book

Potentially many-to-many. One author can write multiple books; one book can have multiple authors.

These examples demonstrate that relationship types aren’t merely academic concepts — they model real business rules and constraints.

Database Relationships in DBMS Assignments

Students encounter database relationships in various assignment contexts:

Common Assignment Tasks:

• Creating ER diagrams with correct relationship notation
• Converting ER diagrams to relational schemas
• Identifying relationship types from problem descriptions
• Implementing relationships in SQL
• Normalizing tables while preserving relationships

What to Check in Assignments:

1. Entities — Are all entities identified?
2. Relationships — Are relationships correctly typed?
3. Cardinality — Is cardinality accurately represented?
4. Keys — Are primary and foreign keys properly defined?
5. Tables — Do tables reflect the relationships?
6. Foreign Keys — Are they in the correct tables?

For students seeking additional support, DBMS assignment help and database assignment help services can provide guidance on complex relationship modeling tasks. Understanding relationships thoroughly also helps with database design assignment help needs.

How to Check Whether Your Database Relationships Are Correct

Use this checklist to validate your database design:

✅ Does every entity have a purpose?
Remove entities that don’t represent real-world objects or concepts.

✅ Are primary keys defined for every table?
Every table needs a unique identifier.

✅ Are foreign keys correct?
Foreign keys should reference the appropriate parent tables.

✅ Is cardinality correct?
Verify that 1:1, 1:N, and M:N relationships match actual requirements.

✅ Does the ER diagram match the schema?
Documentation should reflect implementation.

✅ Is a junction table needed?
Many-to-many relationships require one.

✅ Are duplicate relationships avoided?
Don’t create multiple paths between the same tables.

✅ Can invalid relationships be prevented?
Constraints should enforce business rules.

✅ Does the database represent real-world requirements?
Test with realistic scenarios.

This checklist helps with how to design database relationships and how to check database design effectively.

Frequently Asked Questions

What are the three types of database relationships?

The three types are one-to-one (1:1), one-to-many (1:N), and many-to-many (M:N). One-to-one means each record in one table relates to one record in another. One-to-many means one record relates to multiple records. Many-to-many means multiple records relate to multiple records, typically through a junction table.

What is a one-to-one relationship in DBMS?

A one-to-one relationship exists when each record in Table A relates to at most one record in Table B, and vice versa. Example: A person has one passport, and a passport belongs to one person. This is implemented by placing a foreign key with a UNIQUE constraint in one of the tables.

What is a one-to-many relationship?

A one-to-many relationship exists when one record in Table A relates to multiple records in Table B, but each record in Table B relates to only one record in Table A. Example: One department has many employees, but each employee belongs to one department. The foreign key goes in the “many” table.

What is a many-to-many relationship?

A many-to-many relationship exists when multiple records in Table A relate to multiple records in Table B. Example: Students take multiple courses, and courses have multiple students. This requires a junction table (like Enrollment) that contains foreign keys to both tables.

What is a junction table?

A junction table (also called bridge table or associative table) is a third table that sits between two tables in a many-to-many relationship. It contains foreign keys referencing both tables and often has a composite primary key. Junction tables may also store attributes specific to the relationship itself.

What is the difference between one-to-many and many-to-many?

In one-to-many, one record in Table A relates to many in Table B, but each B relates to only one A. In many-to-many, multiple records in A relate to multiple records in B. One-to-many needs only a foreign key; many-to-many requires a junction table.

How are relationships created in SQL?

Relationships are created using foreign key constraints. The parent table has a primary key, and the child table has a foreign key that references it. The FOREIGN KEY constraint enforces referential integrity between the tables.

What is cardinality in a database?

Cardinality describes the numerical relationship between entities. It specifies whether one record relates to one (1:1), many (1:N), or multiple records relate to multiple (M:N). Cardinality determines database structure and constraint requirements.

Why are foreign keys important?

Foreign keys enforce referential integrity, ensuring that relationships between tables remain valid. They prevent orphan records, maintain data consistency, and enable JOIN operations to retrieve related data across tables.

How do you represent many-to-many relationships?

Many-to-many relationships are represented using a junction table. This third table contains foreign keys referencing both related tables and typically has a composite primary key made from those foreign keys.

Conclusion

Understanding database relationships is fundamental to effective database design. The mental model is straightforward:

• One-to-One: One record connects to one record
• One-to-Many: One record connects to multiple records
• Many-to-Many: Multiple records connect to multiple records, usually through a junction table

Correct relationships lead to better database design, which produces better data integrity, easier SQL queries, and systems that accurately reflect real-world requirements.

Whether you’re creating ER diagrams, writing SQL, or working on database design assignments, mastering relationship types provides the foundation for everything else in relational databases. Practice identifying relationships in real scenarios, and the patterns will become second nature.

For additional support with database concepts, explore our guides on database keys, database normalization, and SQL JOINs — each builds on the relationship fundamentals covered here.