BCNF, 4NF, 5NF and Beyond: When Is 3NF Not Enough?

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0, Cumulative Update 10).

Guess First: Can a Table in 3NF Still Break?

The bookshop has three stores and organises author signing days. Each store assigns every visiting author a staff member who accompanies them that day; a staff member works only in their own store. The table that holds this looks like this:

CREATE TABLE dbo.signing_hosts (
    author_name NVARCHAR(100) NOT NULL,
    store       NVARCHAR(50)  NOT NULL,
    host        NVARCHAR(50)  NOT NULL,
    CONSTRAINT PK_signing_hosts PRIMARY KEY (author_name, store),
    CONSTRAINT UQ_signing_hosts_author_host UNIQUE (author_name, host)
);
INSERT INTO dbo.signing_hosts (author_name, store, host) VALUES
    (N'Frank Herbert',     N'Kadikoy', N'Grace'),
    (N'Ursula K. Le Guin', N'Kadikoy', N'Grace'),
    (N'Neil Gaiman',       N'Kadikoy', N'Linus'),
    (N'Frank Herbert',     N'Cankaya', N'Margaret'),
    (N'Neil Gaiman',       N'Cankaya', N'Margaret');

SELECT author_name, store, host
FROM dbo.signing_hosts
ORDER BY store, host, author_name;

(5 rows affected)
author_name        store    host
-----------------  -------  --------
Frank Herbert      Cankaya  Margaret
Neil Gaiman        Cankaya  Margaret
Frank Herbert      Kadikoy  Grace
Ursula K. Le Guin  Kadikoy  Grace
Neil Gaiman        Kadikoy  Linus

(5 rows affected)

The primary key is (author_name, store), because an author has a single host in a store. Since a host cannot accompany an author in two stores, (author_name, host) is unique as well. The table has not a single non-key attribute: every column is part of a candidate key. By the measure of the previous post this table is in 3NF (Third Normal Form), because 3NF only talks about non-key attributes. Guess: if Grace moves to another store, can this table suffer an update anomaly?

It can. In this post we look at three such cases where 3NF is not enough: BCNF (Boyce-Codd Normal Form), 4NF (Fourth Normal Form) and 5NF (Fifth Normal Form). Then we take a quick look at 6NF (Sixth Normal Form) and discuss why the target for most OLTP (Online Transaction Processing) schemas is 3NF or BCNF.

BCNF: Every Determinant Must Be a Candidate Key

The table has two dependencies: {author_name, store} → host and host → store. The determinant of the second one, host, is not a key on its own; one host accompanies several authors. Let's verify this with the dbo.attribute_closure procedure from the 5th post of the series. The procedure is the same; only the contents of the dependency table changed:

CREATE TABLE dbo.fds (
    fd_id INT         NOT NULL,
    side  CHAR(3)     NOT NULL CHECK (side IN ('lhs', 'rhs')),
    attr  VARCHAR(30) NOT NULL
);
INSERT INTO dbo.fds (fd_id, side, attr) VALUES
    (1, 'lhs', 'author_name'), (1, 'lhs', 'store'), (1, 'rhs', 'host'),
    (2, 'lhs', 'host'),        (2, 'rhs', 'store');

EXEC dbo.attribute_closure 'author_name, store';
EXEC dbo.attribute_closure 'author_name, host';
EXEC dbo.attribute_closure 'host';

step  attributes
----  ------------------
0     author_name, store
1     host
step  attributes
----  -----------------
0     author_name, host
1     store
step  attributes
----  ----------
0     host
1     store

The closures of {author_name, store} and {author_name, host} reach all three attributes: both are candidate keys. The closure of host, however, is only {host, store}; author_name stays outside. So host determines something, but it does not determine the row.

BCNF's rule forbids exactly this: the left side of every non-trivial dependency in the table must be a superkey. The definition of 3NF had an escape hatch: if the right side of a dependency was part of a candidate key, the left side did not have to be a key. host → store slips through that hatch, because store is part of the first candidate key. BCNF closes the hatch. The rule goes back to the 1974 work of Raymond F. Boyce and E. F. Codd, which is why it carries both names.

The price of leaving the hatch open is the answer to the guess. Grace moves to the Cankaya store and someone updates Le Guin's row:

UPDATE dbo.signing_hosts
SET store = N'Cankaya'
WHERE author_name = N'Ursula K. Le Guin' AND host = N'Grace';

SELECT host, COUNT(DISTINCT store) AS store_count,
       STRING_AGG(store, ', ') AS stores
FROM dbo.signing_hosts
GROUP BY host
HAVING COUNT(DISTINCT store) > 1;

(1 rows affected)
host   store_count  stores
-----  -----------  ----------------
Grace  2            Kadikoy, Cankaya

(1 rows affected)

Grace now seems to work in two stores. Because the "host's store" fact is repeated on every author row, we get the same anomaly we saw with the customer's phone in the previous post. There is an insert anomaly too: you cannot record a newly hired host together with their store until an author is assigned to them.

The Cost of Moving to BCNF: Dependency Preservation

The fix is to move the violating dependency into its own table, exactly as we did for 3NF. host → store goes into a separate table and the author–host pairing remains. Let's undo the update, split, and repeat the two-way EXCEPT check from the previous post:

CREATE TABLE dbo.hosts (
    host  NVARCHAR(50) NOT NULL,
    store NVARCHAR(50) NOT NULL,
    CONSTRAINT PK_hosts PRIMARY KEY (host)
);
CREATE TABLE dbo.author_hosts (
    author_name NVARCHAR(100) NOT NULL,
    host        NVARCHAR(50)  NOT NULL,
    CONSTRAINT PK_author_hosts PRIMARY KEY (author_name, host),
    CONSTRAINT FK_author_hosts_hosts FOREIGN KEY (host) REFERENCES dbo.hosts (host)
);
INSERT INTO dbo.hosts (host, store)
SELECT DISTINCT host, store FROM dbo.signing_hosts;
INSERT INTO dbo.author_hosts (author_name, host)
SELECT author_name, host FROM dbo.signing_hosts;

WITH rejoined AS (
    SELECT ah.author_name, h.store, ah.host
    FROM dbo.author_hosts AS ah
    JOIN dbo.hosts AS h ON h.host = ah.host
)
SELECT 'missing' AS direction, COUNT(*) AS row_count
FROM (SELECT * FROM dbo.signing_hosts EXCEPT SELECT * FROM rejoined) AS m
UNION ALL
SELECT 'spurious', COUNT(*)
FROM (SELECT * FROM rejoined EXCEPT SELECT * FROM dbo.signing_hosts) AS s;

(3 rows affected)

(5 rows affected)
direction  row_count
---------  ---------
missing    0
spurious   0

(2 rows affected)

The split is lossless, because the common column host is the key of the hosts table. Grace's store now lives in a single row. But we lost something. The rule {author_name, store} → host, "an author has a single host in a store", is no longer the key of any table: store is in one table, author_name in the other. While each table is valid on its own, this rule can quietly break:

INSERT INTO dbo.author_hosts (author_name, host) VALUES (N'Frank Herbert', N'Linus');

SELECT ah.author_name, h.store, COUNT(*) AS host_count
FROM dbo.author_hosts AS ah
JOIN dbo.hosts AS h ON h.host = ah.host
GROUP BY ah.author_name, h.store
HAVING COUNT(*) > 1;

(1 rows affected)
author_name    store    host_count
-------------  -------  ----------
Frank Herbert  Kadikoy  2

(1 rows affected)

Herbert now has two hosts in Kadikoy, and both tables accepted the row without complaint. The literature calls this a loss of dependency preservation: the split tables can no longer keep every dependency checkable within individual tables. This is a known cost of BCNF; 3NF can always be reached with a split that is both lossless and dependency-preserving, BCNF cannot always.

In practice you have three options. The first is to leave the table in 3NF and accept the repeated fact. The second is to move to BCNF and hand the rule to application code. The third, in SQL Server, is to bring the lost rule back with an indexed view. If you put a unique clustered index on (author_name, store) over a view that joins the two tables, the rule lives inside the database again:

CREATE VIEW dbo.author_store_hosts
WITH SCHEMABINDING
AS
SELECT ah.author_name, h.store, ah.host
FROM dbo.author_hosts AS ah
JOIN dbo.hosts AS h ON h.host = ah.host;
GO
CREATE UNIQUE CLUSTERED INDEX UX_author_store_hosts
    ON dbo.author_store_hosts (author_name, store);
GO
INSERT INTO dbo.author_hosts (author_name, host) VALUES (N'Frank Herbert', N'Linus');

Msg 2601, Level 14, State 1, Server b8be943222fa, Line 1
Cannot insert duplicate key row in object 'dbo.author_store_hosts' with unique index 'UX_author_store_hosts'. The duplicate key value is (Frank Herbert, Kadikoy).
The statement has been terminated.

I removed the bad row and tried the same INSERT again; this time the index rejected the second Kadikoy row in the joined view. The cost is that every write to author_hosts and hosts also updates the view's index. The rules and cost of indexed views are the topic of the next post; what matters here is that the choice between BCNF and dependency preservation is not a fork in the road but an engineering decision.

4NF: Independent Multivalued Facts

The author page of the book site has two filters: which languages the store has editions of the author in, and which genres the author wrote. Someone puts both in the same table:

CREATE TABLE dbo.author_profiles (
    author_name NVARCHAR(100) NOT NULL,
    language    CHAR(2)       NOT NULL,
    genre       NVARCHAR(50)  NOT NULL,
    CONSTRAINT PK_author_profiles PRIMARY KEY (author_name, language, genre)
);
INSERT INTO dbo.author_profiles (author_name, language, genre) VALUES
    (N'Ursula K. Le Guin', 'en', N'Fantasy'),
    (N'Ursula K. Le Guin', 'en', N'Science Fiction'),
    (N'Ursula K. Le Guin', 'en', N'Essay'),
    (N'Ursula K. Le Guin', 'tr', N'Fantasy'),
    (N'Ursula K. Le Guin', 'tr', N'Science Fiction'),
    (N'Ursula K. Le Guin', 'tr', N'Essay'),
    (N'Frank Herbert',     'en', N'Science Fiction'),
    (N'Frank Herbert',     'tr', N'Science Fiction');

-- German editions arrive; only one row is added
INSERT INTO dbo.author_profiles (author_name, language, genre)
VALUES (N'Ursula K. Le Guin', 'de', N'Fantasy');

SELECT author_name,
       COUNT(*)                                          AS row_count,
       COUNT(DISTINCT language) * COUNT(DISTINCT genre)  AS cross_product
FROM dbo.author_profiles
GROUP BY author_name;

(8 rows affected)

(1 rows affected)
author_name        row_count  cross_product
-----------------  ---------  -------------
Frank Herbert      2          2
Ursula K. Le Guin  7          9

(2 rows affected)

The table's key is all three columns and there are no dependencies; the table is in BCNF. But Le Guin's two languages and three genres needed six rows, because language and genre are independent of each other: every language has to be paired with every genre. When the German editions arrived, the right move was to add three rows; someone added one, and the table now seems to say "Le Guin's German editions exist only in fantasy". The query exposes it: Le Guin has seven rows, while the product of her language and genre counts is nine. When two independent facts live in the same table, the table has to carry a full cartesian product per author to stay consistent.

The name of this structure is a multivalued dependency (MVD): the author determines a set of languages and a set of genres, and the two sets are independent of each other. The notation is author_name ↠ language and author_name ↠ genre. Ronald Fagin defined 4NF in 1977, and the rule is: the left side of every non-trivial multivalued dependency must be a superkey. William Kent's 1983 paper "A Simple Guide to Five Normal Forms in Relational Database Theory" puts it in more everyday language; in our own words: a record should not carry two or more mutually independent multivalued facts about an entity. Let's see what happens when we split:

SELECT DISTINCT author_name, language INTO dbo.author_languages FROM dbo.author_profiles;
SELECT DISTINCT author_name, genre    INTO dbo.author_genres    FROM dbo.author_profiles;

SELECT l.author_name, l.language, g.genre
FROM dbo.author_languages AS l
JOIN dbo.author_genres AS g ON g.author_name = l.author_name
EXCEPT
SELECT author_name, language, genre
FROM dbo.author_profiles;

(5 rows affected)

(4 rows affected)
author_name        language  genre
-----------------  --------  ---------------
Ursula K. Le Guin  de        Essay
Ursula K. Le Guin  de        Science Fiction

(2 rows affected)

When the two split tables are joined, the two rows that were left out appear on their own: Le Guin's German essay and science fiction editions. If language and genre really are independent, this is not "spurious"; it is exactly the information that was entered incompletely. With the two 4NF tables this mistake could never have happened, because adding a new language would have been a one-row job. Kent's growth argument comes from here too: for an author with four languages and ten genres, the combined table carries forty rows, the separated tables fourteen. A normalized design grows additively, a combined one multiplicatively.

The same output shows the other side of the coin as well. If the bookshop says "the German edition exists only in fantasy", language and genre are not independent, there is a relationship between them, and the three-column table is the right model. In that case the split produces two wrong rows. The business rule, not the data, decides which is right; the sentence from the 5th post, "dependencies come from business rules, not from data", holds for multivalued dependencies too.

5NF: Join Dependencies and Splitting into Three

In the 3rd post of the series we split the publishing rights table into two binary relationships, saw spurious rows after joining, and left the question of when a ternary relationship can be split to this post. A similar ternary relationship: the bookshop buys books through distributors, and a row says "this distributor brings this publisher's books to this store". The distributor names are made up for the example. The view below splits the table into two parts and into three parts, joins them back and lists the rows that are not in the original:

CREATE TABLE dbo.supplies (
    distributor NVARCHAR(50) NOT NULL,
    publisher   NVARCHAR(50) NOT NULL,
    store       NVARCHAR(50) NOT NULL,
    CONSTRAINT PK_supplies PRIMARY KEY (distributor, publisher, store)
);
INSERT INTO dbo.supplies (distributor, publisher, store) VALUES
    (N'Atlas',    N'Northwind Books', N'Kadikoy'),
    (N'Atlas',    N'Harbor Press',    N'Kadikoy'),
    (N'Atlas',    N'Northwind Books', N'Cankaya'),
    (N'Meridian', N'Harbor Press',    N'Konak');
GO
CREATE OR ALTER VIEW dbo.supply_split_check
AS
WITH dp AS (SELECT DISTINCT distributor, publisher FROM dbo.supplies),
     ds AS (SELECT DISTINCT distributor, store FROM dbo.supplies),
     ps AS (SELECT DISTINCT publisher, store FROM dbo.supplies),
     two_way AS (
         SELECT dp.distributor, dp.publisher, ds.store
         FROM dp JOIN ds ON ds.distributor = dp.distributor),
     three_way AS (
         SELECT t.distributor, t.publisher, t.store
         FROM two_way AS t
         JOIN ps ON ps.publisher = t.publisher AND ps.store = t.store)
SELECT 'two-way' AS split, distributor, publisher, store
FROM (SELECT * FROM two_way EXCEPT SELECT * FROM dbo.supplies) AS x
UNION ALL
SELECT 'three-way', distributor, publisher, store
FROM (SELECT * FROM three_way EXCEPT SELECT * FROM dbo.supplies) AS y;
GO
SELECT split, distributor, publisher, store
FROM dbo.supply_split_check
ORDER BY split, distributor, publisher, store;

(4 rows affected)
split    distributor  publisher     store
-------  -----------  ------------  -------
two-way  Atlas        Harbor Press  Cankaya

(1 rows affected)

Splitting into two parts (distributor–publisher and distributor–store) produced a spurious row again: Atlas does not bring Harbor Press books to Cankaya. Adding the third pair (publisher–store) removed the spurious row, and the three tables gave back the original exactly. This is not a coincidence; the bookshop's contracts have a symmetric rule: if a distributor carries a publisher, serves a store, and that store sells that publisher, then the distributor brings that publisher's books to that store. While this rule holds, the triple table says nothing beyond the join of the three pairs.

This is called a join dependency (JD): the table can be rebuilt losslessly when it is split into certain parts and joined back. Fagin defined 5NF in 1979; a table is in 5NF if every join dependency in it follows only from its candidate keys. While the symmetric rule holds, the supplies table is not in 5NF and should be split into three pairs. Kent sums up the difference like this: 4NF and 5NF only differ when such a symmetric constraint exists. The benefit of splitting is the same as in 4NF: when Atlas starts serving a new store, you add a single pair row instead of a separate row for every publisher that store sells.

Now let's break the rule. Northwind Books decides to work only with Meridian for the Kadikoy store:

DELETE FROM dbo.supplies
WHERE distributor = N'Atlas' AND publisher = N'Northwind Books' AND store = N'Kadikoy';
INSERT INTO dbo.supplies (distributor, publisher, store)
VALUES (N'Meridian', N'Northwind Books', N'Kadikoy');

SELECT split, distributor, publisher, store
FROM dbo.supply_split_check
ORDER BY split, distributor, publisher, store;

(1 rows affected)

(1 rows affected)
split      distributor  publisher        store
---------  -----------  ---------------  -------
three-way  Atlas        Northwind Books  Kadikoy
three-way  Meridian     Harbor Press     Kadikoy
two-way    Atlas        Harbor Press     Cankaya
two-way    Atlas        Northwind Books  Kadikoy
two-way    Meridian     Harbor Press     Kadikoy
two-way    Meridian     Northwind Books  Konak

(6 rows affected)

The three-way split now produces two spurious rows. Atlas still carries Northwind, still serves Kadikoy, and Kadikoy still sells Northwind; but these three pairs no longer mean "Atlas brings Northwind to Kadikoy". When the rule goes away, the join dependency goes with it, and the triple table, as a single fact that cannot be split, is in 5NF. The publishing rights table in the 3rd post was in this situation too. In practice 5NF violations are rare, because such symmetric rules are rare; but when you meet a ternary relationship, the question to ask is: "Can this triple be derived from its pairs, or is it a decision of its own?"

A Quick Look at 6NF: Temporal Data

The last step of the ladder comes from a different need. 6NF, defined in the 2003 book "Temporal Data and the Relational Model" by C. J. Date, Hugh Darwen and Nikos Lorentzos, splits a table until no non-trivial join dependency remains; in practice every table carries a key and at most one attribute. For OLTP this looks excessive, and usually it is. But it becomes meaningful for effective-dated data: if a book's price changes weekly and its category yearly, keeping both in a single history table means copying the category on every price change. Each lives in its own table with its own rhythm of change:

CREATE TABLE dbo.book_price_history (
    book_id    INT           NOT NULL,
    valid_from DATE          NOT NULL,
    list_price DECIMAL(10,2) NOT NULL,
    CONSTRAINT PK_book_price_history PRIMARY KEY (book_id, valid_from)
);
CREATE TABLE dbo.book_category_history (
    book_id     INT  NOT NULL,
    valid_from  DATE NOT NULL,
    category_id INT  NOT NULL,
    CONSTRAINT PK_book_category_history PRIMARY KEY (book_id, valid_from)
);
INSERT INTO dbo.book_price_history VALUES
    (26, '2026-01-01', 390.50), (26, '2026-02-01', 351.45), (26, '2026-03-01', 390.50);
INSERT INTO dbo.book_category_history VALUES (26, '2025-06-01', 3);

DECLARE @on DATE = '2026-02-15';
SELECT p.book_id, p.list_price, c.category_id
FROM dbo.book_price_history AS p
JOIN dbo.book_category_history AS c ON c.book_id = p.book_id
WHERE p.valid_from = (SELECT MAX(valid_from) FROM dbo.book_price_history
                      WHERE book_id = p.book_id AND valid_from <= @on)
  AND c.valid_from = (SELECT MAX(valid_from) FROM dbo.book_category_history
                      WHERE book_id = c.book_id AND valid_from <= @on);

(3 rows affected)

(1 rows affected)
book_id  list_price  category_id
-------  ----------  -----------
26       351.45      3

(1 rows affected)

The cost is visible too: the question "what was the book's state on 15 February?" needs a separate "latest row as of that date" lookup per attribute. The practical design of effective-dated records, price history and the bitemporal model is in the "Temporal Data: Geçerlilik Tarihli Kayıtlar, Fiyat Geçmişi ve Bitemporal Model" post of the Çözüm Desenleri series. The theoretical link here is this: 6NF is the formal way of keeping "what changed when" in temporal data without copies.

The Whole Normal Form Ladder

Each step includes the previous one: a table in BCNF is in 3NF, a table in 4NF is in BCNF. Each step also removes one more kind of anomaly. But the steps do not cost the same. The steps up to 3NF are almost always free, because the split tables really carry separate facts. BCNF sometimes costs dependency preservation, and 4NF and 5NF only gain you something if the relevant multivalued or join dependency really exists. So the practical target should not be "every table in the highest normal form" but "every table at least in 3NF, deliberate departures from BCNF documented, 4NF and 5NF violations looked for in review".

In Production

A 4NF violation usually hides in a "put everything in one place" table: a user's roles and subscriptions, a product's colours and sizes, an author's languages and genres. The sign in review is a three-column table whose key is all of its columns and which has no non-key attribute. When you see such a table, ask whether two of its columns are independent of each other. To check existing data, the row count versus cartesian product comparison above is a good start; but that query can show a violation, it cannot prove a rule.

BCNF violations usually show up in tables with "overlapping candidate keys": if two composite candidate keys share a column (here author_name), look carefully. In real life this is common in assignment and reservation tables where a resource (host, room, machine) belongs to a single place.

If you use an indexed view to bring back a lost dependency, measure the write path. Every INSERT, UPDATE and DELETE on every table the view depends on also updates the view's index and locks the same rows. On rarely written assignment tables this is negligible; on heavily written tables an application-side check, or staying in 3NF, may be cheaper.

Write normal form decisions next to the schema. A single note like "this table is deliberately in 3NF; the host → store repetition is accepted because..." saves the person who tries to "fix" the table two years later. Ways of keeping descriptions inside the schema are in the last post of the series.

Common Pitfalls

Applying academic normal forms blindly. Splitting every table to 5NF, or even 6NF, means joining ten tables for an order screen. A normal form is valuable when it removes an anomaly produced by a dependency that really exists; splitting a table for a dependency that does not exist only increases the number of joins.

Treating dependent facts as independent and splitting them. As we saw with language and genre, if you separate two facts that are related to each other in the name of 4NF, the join produces wrong rows. Ask the business about the rule before splitting.

Moving to BCNF and forgetting the lost rule. When a table is split, list which dependency is no longer the key of any table and choose a protection for each one: an indexed view, an application check or a deliberate acceptance.

Splitting a ternary relationship into three pairs out of habit. 5NF only suggests the split when a symmetric rule exists; without the rule the triple table is already in 5NF. "Discovering" the rule in the data is just as dangerous: in the four rows of d1.sql the rule held by chance, and a single contract change broke it.

Memorising the 3NF definition only in terms of "non-key attributes". A table whose columns are all part of a key passes 3NF automatically, and that does not mean it is free of anomalies. The signing day table and the language–genre table are two examples.

A Small Experiment

★☆☆ Add a German edition for Herbert to the author_profiles table correctly: how many rows does it take? Do the same in the author_languages table and compare the row counts.

★★☆ Add the rule "a host accompanies at most one author per day" and a signing_date column to the signing day example. Rewrite the dependencies, find the candidate keys with the closure procedure and check whether the table is in BCNF.

★★★ In the supplies example, build the three pair tables while the symmetric rule holds and add a new distributor that also starts serving Konak. How many rows would you have had to add to the triple table, and how many to the pair tables? Then write a check query showing that the join of the three pairs always respects the rule.

We climbed the normal form ladder to the top and saw that every step has a price. In the next post we turn the other way: when is denormalization a performance decision, how do techniques such as persisted computed columns, indexed views and summary tables work, and who keeps copied data consistent?