5NF and Database Design: the normalization beast, tamed

The rethink

Alexey Makhotkin has published a long-form take on fifth normal form (5NF) at the Database Design Book site, and it has been reported that the post runs to roughly 3,900 words. His pitch is simple: 5NF has been taught badly. Historically, explanations pile on abstraction and examples that confuse more than clarify. The aim here is to cut through that fog—strip the pedagogy down to business requirements, logical modeling, then physical schema—and show that many of the headaches disappear.

Examples under the microscope

Makhotkin surveys the usual suspects: Wikipedia’s Traveling Salesman / Brand / Product Type triangle, the decomplexify “ice cream” AB‑BC‑AC example, and Barry Johnson’s “musicians” ABC+D star pattern from Data Demythed. He argues, persuasively, that Wikipedia’s example is particularly unhelpful; it has been reported that the encyclopedia’s baseline explanation fails to make the real restrictions and assumptions explicit, which is where the confusion starts. The ice‑cream triangle and musicians star aren’t arcane puzzles — they’re business rules in disguise. Spot the rule, and the schema choices fall into place.

The practical takeaway

The piece insists on a three‑step workflow: start with business requirements, build a logical model, then design the physical tables. Where 5NF enters some texts, Makhotkin shows, you usually don’t need to summon a new normal form if your logical model already captures the constraints. The two canonical patterns—AB‑BC‑AC triangles and ABC+D stars—illustrate typical tradeoffs, including the choice between composite and synthetic primary keys. In short: normalization remains important, but the scary “5NF monster” often shrinks under a clear-headed, requirements-first approach.

Why this matters

For anyone who designs schemas for a living (or teaches others to), this is a useful reality check: complexity often signals sloppy modeling, not a need for more theory. Want to stop treating 5NF like a rite of passage? Read the breakdown and the worked examples — it’s practical, not pedantic. The full post, with extra chapters on extended examples and suggested readings, is available at the source if you want to dig in: https://kb.databasedesignbook.com/posts/5nf/.

Sources: kb.databasedesignbook.com, Lobsters