Many-to-Many Relationships and Bridge Tables
Four students' fees add up to 3,200 pounds; summed after joining through a bridge table they came out as 5,500.
Video #255 ยท Data modelling
Steps in the video
- 0:00 Fees of 3,200 pounds became 5,500 after joining.
- 0:32 The idea
- 1:00 Setup
- 1:20 The data
- 1:44 The bridge table
- 2:15 Walking the bridge
- 2:30 Who is missing, and a repeat
- 2:41 Run the bridge
- 3:08 The double counting trap
- 3:37 Run the trap
- 4:13 What this does not show
- 4:52 Mistakes to avoid
- 5:29 My take and next steps
- 6:00 Recap
Files
- run.py (49 lines)
- school.py (35 lines)
Tested with
Python 3.12.15
What this does not show
Four made-up students in SQLite; the Power BI example is quoted, not run, and no indexes or speed are measured.
Sources
- https://en.wikipedia.org/wiki/Associative_entity
- https://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many
- https://support.microsoft.com/en-us/office/guide-to-table-relationships-30446197-4fbe-457b-b992-2f6fb812b58f
- https://www.sqlite.org/foreignkeys.html
- https://www.sqlite.org/lang_createtable.html
Learning IT by Example.