Junction Table

A junction table (also called an associative table, bridge table, or linking table) resolves a many-to-many relationship between two entities by creating an intermediate table with foreign keys to both. The Relational Model cannot directly represent M:N relationships — they must be decomposed.

Example

Students enroll in many Courses; Courses have many Students:

erDiagram
    students ||--o{ enrollments : "student_id"
    courses ||--o{ enrollments : "course_id"

    students {
        int id PK
        string name
    }

    courses {
        int id PK
        string title
    }

    enrollments {
        int student_id FK
        int course_id FK
        date enrolled_at
        string grade
    }

CREATE TABLE enrollments (
    student_id INT REFERENCES students(id),
    course_id INT REFERENCES courses(id),
    enrolled_at DATE DEFAULT CURRENT_DATE,
    grade VARCHAR(2),
    PRIMARY KEY (student_id, course_id)
);

Key Design Points

• The Primary Key is typically a Composite Key of both foreign keys
• Can add a Surrogate Key (id SERIAL) if the composite key is referenced elsewhere
• Can carry relationship attributes (grade, enrolled_at, role) that belong to the relationship, not to either entity
• Always index both foreign key columns for efficient joins in both directions