Week 6 · Unit 2 Requirements and Production Stages of the Project (3), Create database tables stage (2): relationships between tables: one to one, one to many, many to many, foreign and composite keys · 3 lessons
3
Many to many: a joining table
ICT Secondary 2 Term 1, Unit 2, Topic 2: pp. 45–53
GoalBreak a many-to-many relationship into two one-to-many links with a joining table.
New words
many-to-many · a record of each table may match many records of the otherjoining table · a table that holds the primary keys of the two tables, one pair per rowcomposite key · a primary key made of two or more fields taken togetherpair · one combination of a player number and an activity number
Explanation
A player may join several activities, and an activity has many players. Neither table can hold this link in one field: a single activity field in the players table cannot hold three activities, and a single player field in the activities table cannot hold many players. Some books say a many-to-many link cannot be put in a database program. It can, with one more table, a joining table. The joining table holds two fields, player_id and activity_id, and each row says “this player joins this activity”. The two fields together form the composite primary key: the same pair may never appear twice, although each number alone may repeat. The many-to-many link is now two one-to-many links: one player has many rows in the joining table, and one activity has many rows in it.
players (one)one to many→joining table (many)many to one→activities (one)
Worked examples
player_id
activity_id
meaning
1
1
Sara – Chess
1
2
Sara – Football
2
2
Ali – Football
3
1
Nour – Chess
3
3
Nour – Drawing
4
2
Adam – Football
Here is the joining table. The pair (1, 2) means Sara joins Football. The pair (3, 1) means Nour joins Chess. The pair (1, 2) cannot appear a second time.
Question
Rows found
Answer
who joins activity 2?
players 1, 2, 4
Sara, Ali, Adam
activities of player 3?
activities 1, 3
Chess, Drawing
Trace with the joining table above. Who joins Football (activity 2)? Rows with 2 in activity_id: players 1, 2 and 4, so Sara, Ali and Adam. Which activities does Nour (player 3) join? Rows with 3 in player_id: activities 1 and 3, so Chess and Drawing.
Class activity
Students and school subjects are many to many. On paper draw a students table (3 students), a subjects table (3 subjects) and the joining table with at least five pairs. Mark the composite key and check that no pair repeats.
Practice
1
Why can one field not store a many-to-many link?
One field holds one value, but a record may match many records.
2
What does a joining table hold?
The primary keys of the two tables, one pair per row.
3
What is the composite primary key of the joining table, and what does it forbid?
The two key fields together; the same pair may not appear twice.
4
Into which two relationships is a many-to-many link broken?
Two one-to-many relationships, each between one original table and the joining table.