The other day I saw this table with a question like this:
What is the highest normal form this "World cup results" table is in and why?
By "highest normal form" it is implied BCNF (Boyce-Codd Normal Form), a rule more or less like "Every fact in the table must depend on a key, and only a key, not another column."
So to decide the higher normal forms (there are 3 levels + BCNF), one has to think of the dependencies (*).
They could be thought of in 2 ways:
🏆 Team and Last Match depend on the world cup Year and Final Standing which uniquely identify a row (they're keys)
🏆 Final Standing and Last Match depend on the world cup Year and Team which uniquely identify a row (this pair can be key too)
In plain language:
🏆 Each final standing is occupied by only one team in a given World Cup year.
🏆 A team has only one final standing in a given World Cup year.
BCNF dictates that:
🎖️ Every column that determines another column must be a key. There is no non-key column determining another column.
Meaning that:
🎖️ A team has only one final standing in a given World Cup year. Team alone does not determine final standing or last match - it needs year (and vice-versa: "Year alone...").
BCNF was invented in the 70s to make normalization more precise, prevent dependency-based redundancy and anomalies during insert/update/delete.
But even BCNF is not telling us which pair is the best key:
⭐ Year + Team
⭐ or Year + Standing?
For that we better think: what sounds more natural, accurate and true?
🤔 "In any given World Cup year, a team can appear only once and can have only one final position."
🤔 or "Every World Cup year is expected to produce exactly one winner, one 2nd-place, one 3rd-place, etc."
💡 HINT: think of the eliminated teams (can you believe 4-time world champion Italy didn't qualify this year? 😢)
Thinking beyond that table, we could have separate WorldCupMatch and WorldCupTeam tables.
Then we could store the match date in only one place where we could get the last match via a type of MAX + GROUP BY query.
The criteria for that would be: Do we want to treat match as a real event with its own facts? Or are we just interested in the standings summary?
---
* skipping the first normal form check
