DataslopeMany-to-Many Relationships
How to recognize relationships where both sides can connect to many rows, and why they must be resolved with an intermediate table.
A student can take many courses. A course can contain many students. Neither side is naturally the single parent of the other.
That is a many-to-many relationship: many rows on the left can connect to many rows on the right. It is common, but it cannot be stored directly with one foreign key.
Seeing both sides as many
Start with the real-world picture, before tables. Ada takes Math and History. Grace takes Math and Art. Math has both Ada and Grace.
The links cross because neither side is single. If you ask from the student side, the answer can be many courses. If you ask from the course side, the answer can be many students.
In conceptual ER notation, you may draw it directly like this:
That diagram is useful for conversation, but it is not the final PostgreSQL table design. Tables need columns, and one column can hold one value in one row.
Why one foreign key cannot express it
Suppose you put course_id on students. Then each student row has
room for one course id.
Now try the opposite: put student_id on courses. Each course row
has room for one student id.
A single foreign key can make one side many, but it forces the other side to be one. Many-to-many needs a place where each pairing can become its own row.
Quick check
Why can't students.course_id fully model students and courses when students take many courses and courses have many students?
Because foreign keys cannot reference tables named courses.
Because each student row would have only one course_id slot, but a student may need many course links.
Because students and courses must always be stored in the same table.
Because PostgreSQL does not allow joins across three tables.
Resolving the design
The relational solution is to add a third table. It stores one row for each student-course pairing. We will study this table in depth on the next page; for now, focus on the shape.
The many-to-many has been replaced by two one-to-many relationships: one student has many enrollment rows, and one course has many enrollment rows.
The new table is not just a technical trick. It names the relationship itself: an enrollment. Once the relationship has a row, the schema has a clear place to store and enforce the connection.
Running the resolved design
This example lists every student-course pairing by joining through the middle table.
Notice that the readable result looks like the original crossing-links picture. The difference is that the database stores each link as a normal row that can be constrained, queried, and extended.
Design questions to ask
When you suspect a many-to-many relationship, ask both directions:
- Can one student take many courses?
- Can one course have many students?
If both answers are yes, do not choose one table to hold the foreign key. Name the relationship and give it a table.
Check your understanding
What makes a relationship many-to-many?
Each row on one side must connect to exactly one row on the other side.
Rows on both sides can each connect to multiple rows on the other side.
The two tables have the same number of columns.
The tables cannot be joined.
In the resolved students-courses design, what does one row in enrollments represent?
One student, with all course details copied into it.
One course, with all student details copied into it.
One pairing between one student and one course.
A summary count of all students in all courses.
Why is the conceptual }o--o{ diagram not enough by itself as a PostgreSQL schema?
Because ER diagrams are illegal in documentation.
Because PostgreSQL can store many-to-many links with invisible columns.
Because the actual schema still needs a table to store each pairing as data.
Because many-to-many relationships should always be ignored.