#255 Data modelling 6.5 min
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.
Predict the trap numbers
Copy the code folder first. Read school.py and run.py, then write down the seven numbers you expect from python run.py trap. Run it and compare. You should see 3200 for the student table, 5500 after the join, 6 and 3 for the two counts, then 2600, 4500 and 2600.
A hint
After the join there is one row per enrolment, and Dana has none.
Enrol Dana in Physics
In school.py, add the enrolment (4, 30, "B") to ENROLMENTS. Predict how each number in the trap run changes, then run python run.py trap. You should see 6100 after the join, 7 and 4 for the counts, and 3200 for the fees of enrolled students, while the two Maths or Art lines stay at 4500 and 2600.
A hint
Dana's fee is 600 and she now appears in exactly one joined row.
Remove the pair key
In school.py, in the enrolment table, change the line grade TEXT, to grade TEXT); and delete the PRIMARY KEY (student_id, course_id)); line below it. Predict what the bridge run does about Ben in Maths twice, then run python run.py bridge. You should see no rejected message at all, because nothing now stops the repeat.
A hint
The unique error came from the primary key on the pair.
Ask your own question
In run.py, add two more ask lines at the end of the trap function that sum the fees of students who take Physics, once with a plain join and once with IN, following the pattern of the two Maths or Art lines. Predict both numbers, then run python run.py trap. Only Chen takes Physics, so you should see 1000 on both lines.
A hint
With a single matching student the join cannot double count.
These tasks are for you to try on your own machine. Nothing is checked or saved.
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.
Up next in Data modelling
More in Data modelling
- #110 Slowly Changing Dimensions in SQL: Type 1 vs Type 2
- #130 Define a Metric Once: Views and a Metrics Layer
- #250 Data Modelling Basics: Entities, Keys, Relationships
- #252 Normalisation 1NF to 3NF on a Messy Spreadsheet
- #253 Primary Keys: Natural, Surrogate and Hash Keys
- #254 Constraints That Catch Bad Data: PK, FK, CHECK
