A Note to Myself on Fifth Normal Form
5NF has been one of those database topics I thought I understood. Textbook examples often start with a three-column table, split it into smaller tables, and ask whether the original can be reconstructed. I end up wondering where that first table came from, and what its rows are supposed to mean.
These are my notes from three articles that finally made it click for me. The examples are theirs, not mine, and I link to each one in the section where I use it. If you want the full argument, read the originals:
- Alexey Makhotkin, Historically, 4NF explanations are needlessly confusing
- Alexey Makhotkin, 5NF and Database Design
- Barry Johnson, 5NF: The Missing Use Case
Step 1: 4NF is just two junction tables
Source: Makhotkin, Historically, 4NF explanations are needlessly confusing.
Makhotkin starts with an ordinary requirement. We’re building a site to find sports instructors. Each instructor teaches some skills and speaks some languages. Nobody would think twice about designing it like this:
instructor_skills instructor_languages
instructor_id | skill_id instructor_id | lang_id
--------------+-------------- --------------+--------
2 | yoga 2 | en
2 | pilates 3 | en
3 | weightlifting 3 | it
3 | crossfit 3 | fr
3 | boxing 5 | enTwo columns each, with both columns together as the primary key. His advice to a junior colleague is basically: if two things have a many-to-many link, make a table with the two IDs, and don’t be afraid of making more tables.
That design is already in 4NF. You don’t need the theory to get there.
So why do 4NF explanations feel so hard? Because almost every one of them goes backwards. They show a weird three-column table first, “decompose” it into two tables, and then announce that the result is in 4NF. Makhotkin uses William Kent’s 1983 paper as the example. Kent puts (employee, skill, language) into one record: Smith can cook and type, and speaks French, German, and Greek. In that table “cook” and “French” end up on the same row even though they have nothing to do with each other. Kent then walks through five different ways to arrange those rows, all awkward.
Makhotkin’s question is why anyone starts with that table at all. Nobody would build it today. He finds the same pattern in Fagin’s original 1977 paper, in Wu’s 1992 paper, on Wikipedia (restaurant, pizza, delivery area), and in ChatGPT’s answer. His guess is that back then people really did build these tables (Wu found 4NF violations in 9 of the 40 databases she studied), so the contrast was needed. Today it mostly confuses people.
He also translates “multivalued dependency” into plain language: it’s just a set of IDs. “Instructor 3 speaks [en, it, fr]” is the whole idea.
What I’m keeping from this: 4NF means “don’t put two unrelated lists in the same table.” Two independent many-to-many links get two junction tables.
Step 2: what 5NF actually asks
The formal definition says every nontrivial join dependency must be implied by the candidate keys. I find it easier to turn that into a question:
If I split this table into smaller ones and join them back, do I get exactly the same rows, with none missing and none made up?
4NF was about two lists that are unrelated. 5NF is about three things that are all related in pairs, and whether the three-way combination tells you anything the pairs don’t.
In 5NF and Database Design, Makhotkin says this shows up as one of two shapes:
- A triangle (he calls it AB-BC-AC): three entities, joined in pairs by three many-to-many tables. The three-way combination can be worked out from the pairs.
- A star (ABC+D): a fourth entity that points at each of the three. The three-way combination is its own fact and has to be stored.
The next two examples are one of each.
Example 1: ice cream, where splitting works
Source: Makhotkin, 5NF and Database Design. He took the example from the Decomplexify normalization video.
The setup: brands make flavours, and we want to record our friends’ ice cream preferences. Each friend likes some brands and some flavours. The rule is that preferences combine. If a friend likes brands A and B and flavours 1 and 2, they like A-1, A-2, B-1, and B-2, but only where the brand actually makes that flavour.
There are three entities (brand, flavour, friend) and three many-to-many links, so we get three junction tables. This is his data:
brand_flavours friend_brands friend_flavours
brand | flavour friend | brand friend | flavour
----------+----------------- -------+----------- -------+-----------------
Frosty's | Vanilla Jason | Frosty's Jason | Vanilla
Frosty's | Strawberry Jason | Alpine Jason | Chocolate
Frosty's | Mint Choc Chip Suzy | Alpine Suzy | Rum Raisin
Alpine | Vanilla Suzy | Ice Queen Suzy | Mint Choc Chip
Alpine | Rum Raisin Suzy | Strawberry
Ice Queen | Vanilla
Ice Queen | Strawberry
Ice Queen | Mint Choc ChipJason likes chocolate, but none of these brands make it, so there’s no chocolate combination for him.
Because of the rule, every friend-brand-flavour row can be worked out from the three pairs. If we also stored a friend_brand_flavours table, it would only repeat what the pairs already say, and it could drift out of sync with them. That’s the 5NF split, and here it loses nothing.
Makhotkin’s point is that you never need to “apply 5NF” to get here. You list the entities and the links, give each many-to-many link a table, and the design comes out normalized.
Picky friends
He then extends the example. Some friends like one specific flavour from one specific brand, like “Frank loves Coldflash kiwi” but no other Coldflash flavours and no other kiwi. The triangle can’t say that.
He doesn’t replace the three tables. He adds a Preference entity next to them, which points at a friend, a brand, and a flavour (the star shape from the next example). The broad preferences stay in the three junction tables, and the specific ones go in preferences. Both patterns can live in the same database.
Why Wikipedia’s example never clicked for me
Wikipedia’s 5NF example uses travelling salespeople, brands, and product types (Jack Schneider sells Acme vacuum cleaners and breadboxes, Mary Jones sells Robusto pruning shears and vacuum cleaners, and so on). It has the same structure as the ice cream example, but the rule it needs is that a salesperson who sells brands B1 and B2, and sells product type P, “must offer product type P from both brands.”
Makhotkin points out that no business works that way. It would mean that if you start selling Robusto vacuum cleaners, you’re forced to sell Acme ones too. Since the rule makes no sense, the split looks like a trick. With ice cream preferences the same rule feels normal.
Example 2: musicians, where splitting breaks things
Source: Makhotkin, 5NF and Database Design, using the example from Barry Johnson’s 5NF: The Missing Use Case.
We want to record which musician played which instrument at which concert. Makhotkin’s reason for building it: paying people. Violinists get paid a fee, and someone who plays cymbals in the first half and a gong at the end gets paid for both.
In plain English we’d say “a musician played an instrument at a concert,” but that sentence links three things at once. He looks for the hidden noun and calls it a Performance. A concert has many performances, and each performance has one musician and one instrument. In the logical model that’s three one-to-many links, which become three columns in one table:
CREATE TABLE performances (
concert_id INTEGER NOT NULL,
musician_id INTEGER NOT NULL,
instrument_id INTEGER NOT NULL,
PRIMARY KEY (concert_id, musician_id, instrument_id)
);concert | musician | instrument
---------------+----------+-----------
nye-2024 | Patricia | violin
christmas-2025 | Marc | violin
christmas-2025 | Gilles | violin
christmas-2025 | Vlad | viola
christmas-2025 | Yovan | celloHe also shows a version with a synthetic id primary key plus UNIQUE (concert_id, musician_id, instrument_id). The composite key only works because the business says a musician gets paid for an instrument once per concert. If that weren’t true, say in a game where a user can gift the same item to the same friend several times, you’d need the synthetic ID. It would still be the same star shape.
Here the combination is the fact, so it can’t be split into pairs. To check this for myself, I took his rows and imagined we also had a “who can play what” table where Marc plays violin and viola. Then the three pairs would all be true for (christmas-2025, Marc, viola): Marc was at the concert, a viola was at the concert, and Marc can play viola. Joining the pairs would give us a performance that never happened. That’s what a lossy split looks like.
So this is the same three-column table the textbooks use, and here it’s correct as it is. Makhotkin gets there the same way as with ice cream, by modelling the business and building the tables directly, without reasoning about 5NF.
The same example, with different definitions
Source: Barry Johnson, 5NF: The Missing Use Case.
Johnson’s article is where the musician example comes from, and it goes somewhere else with it.
He describes the usual textbook treatment like this. Show Performance{Performance#, Concert#, Instrument#, Musician#} and assume it’s in 4NF. Offer one alternative, three binary relationships:
Appearance{ Appearance#, Concert#, Musician# }
Inclusion { Inclusion#, Concert#, Instrument# }
Skill { Skill#, Instrument#, Musician# }Then show, correctly, that you can’t rebuild Performance from those three, and conclude that the original must be in 5NF.
His complaint is that the textbook never says what a concert, musician, instrument, or performance is. Without definitions you can’t decide anything. So he makes up some reasonable ones by imagining talking to users:
- A singer’s voice counts as their instrument.
- A conductor’s instrument is the baton, real or imagined.
- Only musicians playing an instrument are part of a performance, and an instrument isn’t in a performance unless someone plays it.
With those rules, a musician at a concert who isn’t playing anything, or an instrument at a concert with no player, can’t happen. Appearance and Inclusion aren’t needed. Musician-instrument pairs (skills) exist before any concert happens, so the model becomes:
Skill { Skill#, Instrument#, Musician# }
Performance{ Performance#, Concert#, Skill# }A performance now points at a skill instead of at a musician and an instrument separately. By his definitions, the original table wasn’t in 5NF after all.
The part I liked most is how 2NF connects to this. Textbook 5NF examples hide all the non-key columns behind ”…”. Johnson adds one that’s easy to imagine, a SkillRating used to decide who to invite to a concert:
0NF: { ConcertName, InstrumentName, MusicianName, SkillRating }SkillRating depends on the musician and instrument, not on the concert. That’s a plain 2NF problem, and fixing it pulls out Skill, which lands on the same model as above. His experience is that real models nearly always have columns like this, so the earlier normal forms usually reshape the model before 5NF ever comes up.
He also has a second example: subject, teacher, and textbook. It’s the same three-way shape, but he says the right answer depends on things like whether the curriculum prescribes textbooks or the teacher picks them, and whether teachers need a qualification in a subject. Different answers give different tables.
Makhotkin’s reply about skill
Makhotkin discusses Johnson’s Skill model at the end of his 5NF article and disagrees a bit. He thinks “a musician can play an instrument” is a real link, but it belongs to a different business process. Someone could play at a concert without listing it as a skill, like whoever hits the hammer in Mahler’s 6th Symphony, which happens two or three times in the whole piece. And someone could list a skill and not have played anywhere yet. So he keeps performances as it is and adds a separate musician_skills junction table that doesn’t depend on it.
I don’t think one of them is wrong. They’re modelling different businesses. If you’re running a marketplace for hiring musicians, you need skills. If you’re recording who played what and paying them, you need performances. You might need both. That’s the point both articles make: the definitions decide the tables.
What I’m taking away
- If I can’t write down in one sentence what a row means, I’m not ready to normalize the table.
- 4NF: two unrelated lists don’t belong in one table.
- 5NF: ask whether the three-way fact can be worked out from the pairs (ice cream) or is its own fact (performances).
- Try to break it. Find a case where all three pairs are true but the three-way row is false. If I can, the three-way table stays.
- If a pair like musician-instrument has its own columns or exists without the third thing, it’s probably its own entity.
- Don’t split a table just because a textbook example shows a split.
All the examples here come from Alexey Makhotkin’s 4NF and 5NF articles and Barry Johnson’s 5NF: The Missing Use Case. Read them, they’re better than my notes.