A junction table, also known as an associative table or a bridge table, is a table used to manage many-to-many relationships between two other tables in a relational database.

In SQL, many-to-many relationships cannot be directly implemented using just two tables because each table’s foreign key can only relate to one other table’s primary key. Instead, a junction table is created to “link” the two tables together.

Example Scenario: Books and Authors

Consider a scenario where we have two entities: Books and Authors. A book can have multiple authors, and an author can write multiple books. This is a classic many-to-many relationship.

In this case, we could create a third table – a junction table – to “link” them together.

It might look like this:

Here, the AuthorsBooks table is the junction table.

This is an example of how a junction table can enable a many-to-many relationship. The AuthorsBooks uses the primary keys from each table as its primary key (making it a compound key). This allows us to associate multiple books with a single author and multiple authors with a single book.

Here’s an example of code that creates the above setup.

1. Books Table

CREATE TABLE Books (
    BookID int PRIMARY KEY,
    Title nvarchar(255)
);

2. Authors Table

CREATE TABLE Authors (
    AuthorID int PRIMARY KEY,
    AuthorName nvarchar(255)
);

3. BookAuthors (Junction Table)

To manage the many-to-many relationship, we create a junction table that references the primary keys from both Books and Authors:

CREATE TABLE BookAuthors (
    BookID int,
    AuthorID int,
    PRIMARY KEY (BookID, AuthorID),
    FOREIGN KEY (BookID) REFERENCES Books(BookID),
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

So the references to the other two tables are done as foreign key constraints. These constraints ensure that any value that goes into these columns already exists in the primary key of the referenced table. For example, if we insert a value of 1 into BookAuthors.BookID, then there must already be a value of 1 in the Books.BookID column.

Junction Tables with a Surrogate Key

The above junction table example consists of a compound primary key. But it’s certainly possible to use a surrogate key on a junction table instead.

For example, we could change it to the following:

CREATE TABLE BookAuthors (
    ID int IDENTITY PRIMARY KEY,
    BookID int,
    AuthorID int,
    FOREIGN KEY (BookID) REFERENCES Books(BookID),
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

Here, I included an extra column called ID, which I set as the primary key. In this case I was using SQL Server and so I used the IDENTITY property to make it an “identity column”, or one with an auto-incrementing column.

In other RDBMSs you might use a slightly different syntax. For example in MySQL and MariaDB you can use AUTO_INCREMENT to make an automatically incrementing column. In PostgreSQL you could make the column a SERIAL datatype, which will automatically create a sequence that automatically increments the value in the column.

Actually now that I think of it, MySQL and MariaDB also have a SERIAL type.

Either way, the point is that we can use a surrogate key for the primary key instead of using a compound key if required.

Including Other Columns in Junction Tables

While it’s quite possible (and normal) for a junction table to consist solely of a compound primary key (and no other columns), additional columns can be included if required. These might represent attributes specific to the relationship itself rather than to the entities being linked.

For example, we could add a ContributionPercentage column to our BookAuthors table in order to indicate each author’s contribution to a specific book.

Example:

   CREATE TABLE BookAuthors (
       BookID int,
       AuthorID int,
       ContributionPercentage decimal(5,2),
       PRIMARY KEY (BookID, AuthorID),
       FOREIGN KEY (BookID) REFERENCES Books(BookID),
       FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
   );

When to Use a Junction Table

Use a junction table whenever you need to model a many-to-many relationship between two entities in your database. It’s essential when neither entity’s primary key can naturally reference multiple records in the other table.

In SQL, a foreign key in a table can relate to a primary key in another table, establishing a one-to-one or one-to-many relationship. However, when dealing with a many-to-many relationship, a single foreign key isn’t sufficient because one record in a table can be associated with multiple records in another table, and vice versa. That’s where a junction table comes into play, using a combination of foreign keys to link the primary keys of the related tables.

Example Use Cases:

• Books and Authors: A book can have multiple authors, and an author can write multiple books. We’ve seen by the examples above how a junction table can be used to enable this relationship.
• Students and Courses: A student can enroll in multiple courses, and a course can have multiple students.
• Products and Orders: A single order can contain multiple products, and a product can be part of multiple orders.
• Teachers and Classes: A teacher can teach multiple classes, and a class can be taught by multiple teachers.
• Actors and Movies: An actor can appear in multiple movies, and a movie can feature multiple actors.
• Tags and Articles: An article can have multiple tags, and a tag can be associated with multiple articles.
• Employees and Projects: An employee can work on multiple projects, and a project can involve multiple employees.
• Musicians and Bands: A musician can be part of multiple bands, and a band can have multiple musicians.
• Clubs and Members: A club can have multiple members, and a member can join multiple clubs.
• Vehicles and Drivers: A vehicle can be driven by multiple drivers, and a driver can operate multiple vehicles.
• Customers and Products: A customer can purchase multiple products, and a product can be purchased by multiple customers.

These examples highlight the versatility of junction tables in modeling many-to-many relationships across various domains.