5NF and Database Design

It argues that Wikipedia's 5NF example is overly artificial and doesn't help in understanding actual business rules. Instead, it proposes an order of first establishing a logical model, and then designing a normalization-preserving physical schema.

The key is not to force-memorize 5NF, but to decompose relationships into precise links to build tables. In this process, it summarizes that two recurring patterns emerge.

• AB-BC-AC triangle

  • In the ice cream example, there are 3 anchors—Brand, Flavour, and Friend—and M:N link tables such as brand_flavours, friend_brands, and friend_flavours are created among them.
  • A friend's preferences are interpreted as the intersection of brand and flavour, and there may also be entities that actually exist but are not linked.

• ABC+D star pattern

  • In addition to concert, musician, and instrument, an additional anchor called Performance is needed.
  • It's organized as a 1:N link-centered structure with concerts, instruments, musicians, and performances, where performances has concert_id, instrument_id, and musician_id.
  • The natural-language sentence "a musician plays an instrument at a performance" contains 3 anchors, so this must be decomposed and handled during design.

In conclusion, it argues that a good explanation of 5NF should not assume strange constraints, but should first define business meaning, and then show how to translate that meaning into tables in the simplest and most integrity-preserving way.