Primary Keys and Foreign Keys: Building Connections Between Tables

Before choosing a table structure, ask two questions: Can one record on the left connect to multiple records on the right? Can one record on the right connect to multiple records on the left? If both answers are yes, the relationship is many-to-many.

Programming

When One-to-Many Falls Short

A one-to-many relationship works when one record connects to multiple records of another type, while each record on the many side connects back to one record on the other side. A teacher can have many students, and a department can have many employees. However, some real-world connections work in both directions: one record can connect to many records on the opposite side, and each of those records can also connect to many records on the first side.

Key point

Reading Cardinality in an ER Diagram

Cardinality describes how many records on one side can be connected with records on the other side. In an entity-relationship diagram, symbols appear at the ends of the line connecting two tables. A one-to-many relationship has a single indicator at one end and a crow's foot, or many indicator, at the other. A many-to-many relationship has a many indicator at both ends.

How does the direction of multiplicity differ between a one-to-many relationship and a many-to-many relationship?

Relationship	Multiplicity at the first side	Multiplicity at the second side	Meaning
One-to-many	One	Many	One record connects to multiple records of the other type.
Many-to-many	Many	Many	Records on both sides can connect to multiple records on the opposite side.

Cardinality patterns described in the source material.

Testing the User-Course Relationship

Worked example

Classifying a User and Course Model

Determine whether the relationship between users and courses is one-to-many or many-to-many.

1
Check the user side
A user can belong to many courses, so one user can connect to multiple course records.
2
Check the course side
A course can contain many users, so one course can connect to multiple user records.
3
Compare both directions
Multiplicity exists at both ends. The relationship is therefore many-to-many rather than one-to-many.

Result

Users and courses form a many-to-many relationship.

This classification step should happen before choosing where to place foreign keys. If only one side can connect to multiple records, a foreign key on the many side can represent the relationship. If both sides can connect to multiple records, a single foreign-key column on either original table is not enough.

Why Direct Foreign Keys Fail

A one-to-many relationship can be implemented by adding a foreign-key column to the many side. For example, a student table can have a teacher_id column, so each student record points to one teacher. The same pattern cannot directly represent users belonging to many courses. If the user table receives a course_id column, each user row can point to only one course. If the course table receives a user_id column, each course row can point to only one user. Neither arrangement represents multiplicity in both directions.

What happens when each original table tries to store the other table's reference directly?

The Junction Table Solution

A junction table, also called an associative entity, resolves a many-to-many relationship by introducing a third table. This table contains foreign keys pointing to both original tables. Instead of trying to connect users directly to courses in one column, the junction table stores connections between a user and a course. The original many-to-many relationship is thereby broken into two one-to-many relationships.

How do two one-to-many relationships through an intermediate table allow multiple users to connect to multiple courses?

Worked example

Breaking One Many-to-Many Relationship into Two One-to-Many Relationships

Represent users belonging to many courses and courses containing many users.

1
Create the two original tables
Keep User and Course as separate tables representing the two types of records.
2
Add the junction table
Create an associative entity between them. It contains a foreign key pointing to the User table and another foreign key pointing to the Course table.
3
Read the first relationship
One user can be connected to many records in the junction table, producing a one-to-many relationship.
4
Read the second relationship
One course can also be connected to many records in the junction table, producing a second one-to-many relationship.
5
Recover the original meaning
Following the two relationships allows any user to connect to multiple courses and any course to connect to multiple users.

Result

The junction table represents the many-to-many relationship as two manageable one-to-many relationships.

Design Checks and Common Mistakes

Common mistake
Treating every relationship as one-to-many
What it looks like
Assuming that one user can belong to only one course because a direct course_id column is easy to add.
Why it fails
The user-course scenario allows a user to belong to many courses, and a course to contain many users.
Do this instead
Test multiplicity in both directions before selecting the relationship pattern.
Common mistake
Adding one foreign key to the wrong original table
What it looks like
Adding user_id to the course table and expecting one course row to represent all of its users.
Why it fails
A foreign-key column stores only one value per row, so one course row would point to only one user.
Do this instead
Use a junction table with foreign keys to both original tables.
Common mistake
Missing the ER-diagram signal
What it looks like
Reading a line with many indicators at both ends as a one-to-many relationship.
Why it fails
Many indicators at both ends represent many-to-many cardinality.
Do this instead
Look at both ends of the connecting line: one many indicator means one-to-many; many indicators at both ends mean many-to-many.
Common mistake
Waiting until implementation to identify the relationship
What it looks like
Designing the original tables first and discovering later that both sides need multiple connections.
Why it fails
Changing the model later can require significant rework.
Do this instead
Ask the two multiplicity questions during the design process.

Best practice

Identify many-to-many relationships early. For each proposed connection, check multiplicity from the left side and then from the right side. When both directions are many, plan for an associative entity rather than trying to place the relationship in one direct foreign-key column.

Practice the Recognition Test

MEDIUM

A design has two tables, Authors and Books. One author can be connected to multiple books, and one book can be connected to multiple authors. Identify the relationship type, describe how it would appear in an entity-relationship diagram, and state whether a direct foreign key in either original table is sufficient.

Hints

Check whether multiplicity exists in both directions.
A many-to-many relationship has a many indicator at both ends of the ER-diagram line.
A direct foreign-key column stores only one value per row; consider the role of a junction table.

Your attempt (not saved)

Key Takeaways

1
A one-to-many relationship has multiplicity in one direction; a many-to-many relationship has multiplicity in both directions.
2
An ER diagram shows a many-to-many relationship with many indicators at both ends of the connecting line.
3
A foreign-key column can store only one value per row, so a direct foreign key cannot represent a many-to-many relationship.
4
A junction table, also called an associative entity, contains foreign keys to both original tables.
5
The junction table changes one many-to-many relationship into two one-to-many relationships.

At a glance

Use multiplicity in both directions to recognize a many-to-many relationship.
In an ER diagram, many-to-many relationships have many indicators at both ends of the connecting line.
A single foreign-key column stores one value per row and cannot directly represent multiple connections in both directions.
A junction table resolves the problem by storing foreign keys to both original tables.
The junction table creates two one-to-many relationships that together model the original many-to-many connection.

Related concepts

More concepts starting with P