A many-to-many relationship needs a third table. The intermediate table holds one row per link and carries the primary keys of both sides.
This lesson belongs to relational data modelling. It builds on reading an entity relationship diagram.
Why does one foreign key fail?
Suppose each Student row carries a ClubID. That works only if a student joins one club. Aina joins Chess and Robotics, so her single ClubID cell would need two values.
Swap the direction and the same problem appears: a club has many students, so one StudentID in Club cannot hold them all.
Worked example: students and clubs
Start with the two entities.
Student: S01 Aina, S02 Bala, S03 Chen.
Club: C1 Chess, C2 Robotics.
The facts are that Aina joins C1 and C2, Bala joins C2, and Chen joins C1. Put each link in its own row of a new table, Membership.
| StudentID | ClubID | JoinTerm |
|---|---|---|
| S01 | C1 | Term 1 |
| S01 | C2 | Term 2 |
| S02 | C2 | Term 1 |
| S03 | C1 | Term 1 |
The primary key is the pair (StudentID, ClubID). StudentID repeats (S01 twice) and ClubID repeats (C1 twice), but no pair repeats. Both columns are also foreign keys, pointing to Student and Club.
The result is two one-to-many links: one student has many memberships, and one club has many memberships.
The mistake that costs marks
Students write a list in one cell, such as ClubID = “C1, C2”. It looks compact, but it breaks the one-value-per-cell rule.
| Design | Can you count Chess members? | Can you add a join term? |
|---|---|---|
| list in one cell | no, the cell must be split first | no |
| intermediate table | yes, count rows with C1 | yes, add a JoinTerm column |
The fix is to ask whether both sides say “many”. If they do, create a table that links them.
Check yourself
A school stores Teachers and Subjects. A teacher can teach many subjects, and a subject is taught by many teachers. Name the intermediate table, its columns and its primary key.
Answer
A table such as Teaching, with columns TeacherID and SubjectID. The primary key is the pair (TeacherID, SubjectID). Each column is also a foreign key, pointing to Teacher and Subject.
What to study next
An intermediate table avoids one kind of repeated data. Continue with explaining update anomalies before normalising, then try the relational data modelling practice set.
If you want a teacher to go through your design, see online one-to-one Computer Science tuition.