Beyond 3NF

Every normal form is a rule about functional dependencies. Column A determines column B, written A -> B, when 2 rows that agree on A always agree on B. A candidate key is a set of columns that determines every other column, and a table can have more than 1 candidate key. 3NF requires that every column outside the keys depends on a whole candidate key and on nothing else. The rule says nothing about columns that sit inside a key, and that silence is the gap Boyce-Codd Normal Form closes.

A tutoring marketplace records which tutor a student works with in each subject in sessions(student_id, subject, tutor_id). Two business rules hold: a student has 1 tutor per subject, and a tutor teaches exactly 1 subject.

The rules give 2 dependencies. {student_id, subject} -> tutor_id, because a student has 1 tutor per subject. tutor_id -> subject, because a tutor teaches 1 subject. That second dependency makes {student_id, tutor_id} a second key of the table: knowing the tutor tells you the subject. subject belongs to the key {student_id, subject}, so it counts as a key column, and 3NF exempts any dependency whose right side is a key column. That exemption lets tutor_id -> subject slip through, and the table passes.

Now watch it fail. Tutor T7 moves from Algebra to Geometry: every row carrying T7 must change, 2 rows in this sample and thousands in production, and a job that misses 1 leaves T7 teaching 2 subjects at once. Student S3 drops Algebra: the only row recording that T9 teaches Algebra disappears with the student. A new tutor T11 who teaches Chemistry can't be stored at all until a student signs up, because student_id is part of every key.

tutor_id isn't a candidate key of sessions, because it doesn't determine student_id. So tutor_id -> subject breaks BCNF. The fix moves that dependency into a table where its left side is the key.

tutor_subjects has 1 determinant, tutor_id, and it's the key. student_tutors has a 2-column key and no other columns, so nothing can depend on part of it. T7 moving to Geometry is now 1 row. S3 leaving deletes 1 row in student_tutors while T9 keeps teaching Algebra. T11 gets a row before any student arrives. The original 3-column view comes back with a join.

The price is that the rule {student_id, subject} -> tutor_id now spans 2 tables. A row pairing S1 with T7 and a second row pairing S1 with T9 would give S1 2 Algebra tutors, and neither table can reject the second row on its own. The check becomes a join that counts tutors per student per subject.

Host 41 speaks 2 languages and manages 3 listings, so the table needs 6 rows, 1 per combination. Nothing determines anything: the same host appears with 2 languages and the same language with 3 listings, so the key is all 3 columns and BCNF has no complaint. Now host 41 learns Italian. The complete update inserts 3 rows, 1 per listing. A job that inserts only (41, Italian, L100) leaves the table claiming Italian goes with 1 listing and not the other 2, a claim nobody ever made, because a language belongs to the host and applies to every listing.

The relationship is a multi-valued dependency, written host_id ->> language: for a given host, the set of languages across the rows is the same whatever listing those rows carry. The symmetric dependency host_id ->> listing_id holds for the same reason. 4NF requires that the left side of every such dependency is a candidate key. host_id alone is not a key of host_profile, so the table fails. The repair puts each list in its own table keyed by the host.

Adding Italian is now 1 row in host_languages, and no listing is involved. The saving grows with the lists: a host with 5 languages and 10 listings costs 50 rows in the combined table and 15 across the pair.

Not every 3-column table with repeated values is a cross product. listing_translations(listing_id, language, translated_title) stores the languages a particular listing has been translated into. Adding a Japanese translation to L100 says nothing about L205. The language is a fact about that listing, so the columns stay together, and the table is already in 4NF. One question separates the 2 cases: when you add a value to column B for a given A, must you also add it for every value of column C that A has? If yes, B and C are independent lists and the table splits. If no, B and C form a single fact and the table stays.

When stored_rows equals languages times listings for every host, the table is storing 2 independent lists as a product and belongs in 2 tables. When it falls short for some hosts, either a job skipped rows or the 2 columns share a real relationship, and the data owners can tell you which.

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.

A delivery marketplace keeps orders, customers, cities and regions in 3NF. Revenue by region, the number on every executive dashboard, needs 3 joins.

At 2 billion orders and 40 million customers, the first join alone ships the customer table to every worker and probes it 2 billion times. With region_name copied onto each order row, the query reads 1 table, groups, and finishes in a fraction of the time. The copy makes each region name appear on every order in it, which is the redundancy 3NF removed. Whether that's a problem depends on 5 questions.

Region names change about never, the dashboard runs thousands of times a day, the warehouse pipeline owns orders, and a region rename that lands in the next hourly refresh hurts nobody. Every answer lands in the copy column. Contrast a customer_balance copied onto orders: it changes with every payment, a payment service writes it, and a stale value sends the wrong collections email. Every answer lands in the join column.

A copy is never a source of truth. regions remains the only place a region name is written, and orders carries a derived value that a job can regenerate from regions at any time. When the copy disagrees with the source, the fix is to rerun the job. The failure to design against is an application writing straight into the wide table: now orders says North and regions says Northeast, each has a writer, and the update anomaly from the start of this lesson is back with 2 owners and no referee.

One attribute from a parent table is copied onto each child row. The region example is this pattern: region_name lands on orders. The build is a query the pipeline runs on every refresh.

A number a query would compute from many rows is computed once and stored on the parent. order_total on orders is the sum of its lines. order_count on customers counts that customer's orders. Every read of the number skips the aggregation, and the pipeline overwrites the stored column from a query like this one.