Database Relationships | 1:1, 1:N, M:N Schema Design

# CHAPTER 8

One-to-One, One-to-Many, and Many-to-Many Relationships #

1. Introduction #

2. Learning Objectives #

• Architect a One-to-One (1:1) physical schema.

• Architect a One-to-Many (1:N) physical schema.

• Architect a Many-to-Many (M:N) physical schema.

• Understand the structural necessity of a Junction (Pivot) Table.

• Execute real-world relationship mapping.

3. Architecting One-to-Many (1:N) #

The Rule: The Foreign Key ALWAYS goes on the "Many" side. If 1 Department has Many Employees, the department_id Foreign Key goes inside the Employees table.

Why? Because a single row in the Department table cannot physically hold a list of 50 employee IDs (that violates relational rules). But 50 distinct Employee rows can easily all hold the number 1 in their department_id column!

4. Architecting One-to-One (1:1) #

The Rule: The Foreign Key goes on either table, but it MUST have a UNIQUE constraint applied to it. We place user_id inside the tax_records table. By making it UNIQUE, we mathematically guarantee that no two tax records can ever point to the same user, enforcing a strict 1:1 limit.

CREATE TABLE tax_records (
    id INT PRIMARY KEY,
    ssn VARCHAR(11),
    user_id INT UNIQUE, -- The UNIQUE constraint makes it 1:1!
    FOREIGN KEY (user_id) REFERENCES users(id)
);

5. Architecting Many-to-Many (M:N) #

The Problem:

• If you put the class_id in the Student table, the student can only take 1 class.

• If you put the student_id in the Class table, the class can only have 1 student.

The Solution: You cannot link them directly. You MUST create a third, completely new table in the middle. This is called a Junction Table (or Pivot Table, or Bridge Table).

6. The Junction Table (Pivot Table) #

-- Table A (Students)
-- Table B (Classes)

-- Table C: The Junction Table (enrollments)
CREATE TABLE enrollments (
    student_id INT,
    class_id INT,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (class_id) REFERENCES classes(id),
    
    -- Best Practice: A Composite Primary Key!
    PRIMARY KEY (student_id, class_id)
);

*(Now, if Student #5 wants to take Class #10, you just insert a row into the Junction Table: [5, 10]. If they also want to take Class #12, you insert another row: [5, 12]. Flawless architecture!)*

7. Mini Project: School Management System #

1. 1. Teachers to Departments (1:N):

• *Rule:* 1 Department has Many Teachers.

• *Design:* department_id goes on the teachers table.

1. 2. Teachers to Principals (1:1):

• *Rule:* 1 School has 1 Principal.

• *Design:* school_id goes on the principals table, marked as UNIQUE.

1. 3. Teachers to Students (M:N):

• *Rule:* Teachers teach Many Students, Students have Many Teachers.

• *Design:* Create a Junction Table called teacher_student_assignments containing teacher_id and student_id.

8. Common Mistakes #

• Placing Arrays in Columns: A beginner dealing with a Many-to-Many relationship (like a Blog Post having many Tags) might try to solve it by putting a tags column in the posts table and inserting "[tech, coding, sql]". This destroys the relational database. You can never JOIN or search that data efficiently. You MUST use a post_tag_pivot Junction Table.

9. Best Practices #

• Naming Junction Tables: If the Junction table has no specific real-world name (like enrollments), industry standard is to name it by combining the two parent tables in alphabetical order: e.g., class_student or article_tag.

10. Exercises #

1. 1. In a One-to-Many relationship between Authors and Books, which table physically contains the Foreign Key column?

1. 2. What specific SQL constraint must be added to a Foreign Key to enforce a strict One-to-One relationship?

11. Database Design Challenges #

12. MCQ Quiz with Answers #

13. Interview Questions #

• Q: Explain the structural mechanics of resolving a Many-to-Many relationship. Why is attempting to solve it using Comma-Separated Values (CSV) in a single column considered a catastrophic anti-pattern?

• Q: Walk me through the exact DDL schema design for a One-to-One relationship between a User table and a Driver_License table. How do you guarantee mathematically that a user cannot have two licenses?

14. FAQs #

15. Summary #

16. Next Chapter Recommendation #