Fifth Normal Form (5NF)

4NF splits 2 independent lists. 5NF handles 3 lists tied together by a rule, and the rule decides whether a 3-column table is a fact in its own right or a product that a join could rebuild.

A creator marketing platform tracks sponsorships: a creator promotes a brand on a platform. sponsorships(creator_id, brand, platform) has a 3-column key and no functional or multi-valued dependency, so it passes BCNF and 4NF. Whether it passes 5NF depends on 1 sentence from the business. Suppose the sentence is: a creator promotes a brand on every platform where that creator is active and that brand advertises. Under that rule, every row of sponsorships follows from 3 smaller facts: which brands a creator works with, which platforms a creator is active on, and which platforms a brand advertises on.

C1 works with Acme and Zest, is active on Video and Audio, and both brands advertise on both platforms, so C1 gets 4 rows. C2 works with Acme alone and is active on Video alone, so C2 gets 1. When C1 joins a third platform where both brands advertise, the rule demands 2 new rows. A creator with 5 brands on 4 platforms costs 20 rows, all of which follow from 9 facts about this creator (5 brands, 4 platforms) plus the brand-platform facts that every creator shares. The join dependency *({creator_id, brand}, {creator_id, platform}, {brand, platform}) states that the table equals the join of its 3 projections. 5NF requires that every join dependency follows from the keys; here it follows from a business rule instead, so the table fails 5NF and splits 3 ways.

SELECT
    cb.creator_id,
    cb.brand,
    cp.platform
FROM creator_brands cb
JOIN creator_platforms cp
    ON cb.creator_id = cp.creator_id
JOIN brand_platforms bp
    ON bp.brand = cb.brand
    AND bp.platform = cp.platform

The spurious row

The split works only while the rule holds. Suppose deals are negotiated 1 at a time: C1 promotes Acme on Video only and Zest on Audio only, even though C1 is active on both platforms and both brands advertise on both. The pair tables record C1 with Acme, C1 with Zest, C1 on Video, C1 on Audio, and each brand on each platform. Joining them produces 4 sponsorships where 2 exist.

The 2 extra rows are spurious tuples: facts the join invents because the pieces no longer determine the whole. When each deal is its own agreement, the triple is the fact, the table is already in 5NF, and splitting it destroys information.

Most 3-column tables in a warehouse are genuine facts: an order line, a deal, a session. They're already in 5NF and nobody needs to touch them. 5NF matters when a table turns out to be a product of pairs and a pipeline writes 5 rows when a creator with 5 brands joins 1 new platform, an event that changed 1 fact. The query below tests the rule against real data by comparing the rows the pairs predict with the rows stored.

SELECT
    (SELECT COUNT(*) FROM sponsorships) AS stored_rows,
    COUNT(*) AS predicted_rows
FROM creator_brands cb
JOIN creator_platforms cp
    ON cb.creator_id = cp.creator_id
JOIN brand_platforms bp
    ON bp.brand = cb.brand
    AND bp.platform = cp.platform

Equal counts over months of data make the rule credible and the split safe. A stored count below the predicted one means some combinations are deliberately absent, and the triple stays where it is.

A logistics company stores routes(courier_id, zone_id, vehicle_type). The dispatch rule: a courier may serve a zone with a vehicle type whenever the courier is certified for that vehicle, the courier is assigned to that zone, and the zone permits that vehicle. Which design follows from the rule?

• Store courier_vehicles, courier_zones and zone_vehicles, and build routes with a 3-way join whenever a query needs it.
• Store courier_vehicles and courier_zones only, since a vehicle the courier is certified for is allowed wherever the courier goes.
• Keep routes as the only table, because a 3-column key can never be decomposed without losing rows on the way back.
• Store routes and the 3 pair tables together, and run a nightly comparison job that reconciles them when they drift.

About this lesson

This section is part of the Beyond 3NF lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.