Sixth Normal Form and the Cost of One Attribute per Relation

The classical normal forms remove redundancy by decomposing on a dependency that a relation violates. Sixth normal form inverts the strategy: it decomposes until no relation can be decomposed further, regardless of what dependencies it satisfies. The result is a schema in which every relation consists of a key and at most one non-key attribute, and in which the update anomalies that the earlier normal forms chase individually cannot arise at all — because there is no relation with two non-key attributes whose values could become inconsistent with each other. The cost is a join per attribute in every query, and the interesting engineering question is when that cost is worth paying.

When a relation cannot be split further

A relation is in sixth normal form when every join dependency it satisfies is trivial — that is, when some component of the dependency is the entire heading. Fifth normal form permits nontrivial join dependencies provided they follow from the keys; 6NF permits none at all. The condition is therefore not another rung on the same ladder but a strengthening that rules out the entire class, and it has an equivalent structural reading: the relation cannot be decomposed losslessly into two relations that both have fewer attributes and a non-empty proper subset of the heading.

The structural reading is what makes the form recognisable in a schema. A relation whose heading is a key K together with two or more non-key attributes A1, …, An always admits a lossless two-way decomposition: split it into a relation on K ∪ {A1} and a relation on K ∪ {A2, …, An}, and the join over K reconstructs the original exactly. A relation on K ∪ {A} with a single non-key attribute admits no such split: every two-way partition of its heading either makes one side a subset of the other, which is not a decomposition at all, or leaves the two sides with no attribute in common, so their join is a Cartesian product and is lossy. So the form is reached when no relation has two non-key attributes left to separate.

The decomposition is mechanical. A relation R(key, a, b, c) with no dependencies other than those on the key is already in 5NF, yet it splits into R1(key, a), R2(key, b), R3(key, c). None of those splits again without losing the key-attribute association: dividing (key, a) into (key) and (a) gives two relations with nothing in common, and their join is the Cartesian product of every key with every value of a.

So 6NF is reached by repeatedly applying the same rule: for a relation with key K and non-key attributes a1, …, an, produce n relations, each containing K and one of those attributes. The decomposition is lossless by construction, since every pair of a key value with an attribute value came from a row of the original, and each new relation’s only dependency is the key determining its single non-key attribute.

Each resulting relation is trivially in 5NF, since its only join dependencies are ones whose components include the whole heading. What the decomposition does not preserve is any constraint that related two non-key attributes: such a constraint spanned the original relation and now spans several, and no single relation’s declaration can express it. That loss, not the anomaly count, is what distinguishes 6NF from the forms before it.

Why one attribute per relation removes the anomalies

The anomalies the earlier normal forms address share a shape: two non-key attributes in one relation constrained to be consistent, where a partial update breaks the consistency. An insertion anomaly arises when a fact about one attribute cannot be recorded without inventing a value for another; a deletion anomaly when deleting a fact about one attribute destroys a fact about another; an update anomaly when a change to one attribute must be propagated across rows.

With one non-key attribute per relation, there is no “another attribute” for a fact to be entangled with. Each relation stores exactly one fact: this key has this value for this attribute. Inserting a value requires only the key; deleting a value removes only that value; updating a value touches only rows of one relation.

The consequence is stronger than the sum of the individual normal form guarantees. BCNF removes anomalies caused by functional dependencies on non-superkey attribute sets. 4NF removes those caused by multivalued dependencies. 5NF removes those caused by join dependencies. Each is defined over one dependency class and is silent about the rest. A 6NF schema is silent about nothing, because there is no relation left in which two non-key attributes coexist, and every anomaly of the earlier kinds requires exactly that coexistence. The redundancy is not detected and removed; it is made unrepresentable.

The same property is the cost. A rule such as “if status is closed then closed_at must be present” relates two non-key attributes. In a single relation it is a row-level CHECK that the engine evaluates per write. In a 6NF schema the two values live in different relations and no CHECK can see both, so the rule becomes a trigger, a deferred assertion, or an application invariant. The schema has made the contradictory state unrepresentable only in the sense that it no longer has a row in which to represent it — the two facts can still be inserted in an order that violates the rule, and nothing in the schema notices.

The join cost, quantified

The cost is the join. A query that reads n attributes of a key requires n relations joined on the key, and the join is not free.

An index-nested-loop join over a single key value costs n index probes, one per relation. Each probe is a B-tree descent of depth log_B |Ri|, which for a fanout B in the hundreds and a table of a million rows is two or three page accesses, most of them cached once the upper levels are resident. Call it 2n to 3n page accesses per entity, against a single probe for the same entity stored as one wide row.

The factor is roughly n. Five attributes read together cost five probes instead of one, so a point lookup touches five pages rather than one, and all five must be resident for the lookup to stay cheap. The working set of the hot path grows by the same factor. For a scan the penalty is larger still, since the join order determines whether the n relations are read once each or once per row.

Write cost moves the other way, but less than expected. An insert of a complete row into the unnormalized relation is one write; into the 6NF schema it is n inserts into n relations, each with its own index maintenance. For a heap-organized relation the unnormalized write is one page modification; for the normalized schema it is n modifications in n different pages, which is worse for write amplification and for crash recovery, since the transaction touches n pages rather than one.

-- Reconstructing one entity from a 6NF schema: one relation per attribute.
SELECT k.id,
       a1.value AS name,
       a2.value AS status,
       a3.value AS opened_on,
       a4.value AS closed_on
FROM entity_key AS k
LEFT JOIN entity_name      AS a1 ON a1.id = k.id
LEFT JOIN entity_status    AS a2 ON a2.id = k.id
LEFT JOIN entity_opened_on AS a3 ON a3.id = k.id
LEFT JOIN entity_closed_on AS a4 ON a4.id = k.id
WHERE k.id = ?;

The LEFT JOIN is necessary, not defensive. In a 6NF schema an attribute may be absent for a key — that is how optional attributes are represented, and it is the model’s answer to the null problem discussed in nulls, three-valued logic, and the constraints they break. Absence of a row replaces the null marker, and the outer join reconstructs the absence. An inner join would silently drop entities with any missing attribute, which is a common and serious bug when a normalized schema is queried by someone expecting the unnormalized shape.

Where the design fits

6NF is not a general-purpose schema design. Four situations make it the right choice.

Sparse attributes with high variance. An entity with fifty possible attributes of which any given instance has three is poorly served by a fifty-column relation: most columns are null, and the nulls carry no information. The 6NF decomposition stores only the attributes that exist, and the storage is proportional to the facts rather than to the schema. The alternative is an entity-attribute-value table, which is 6NF with the attribute name stored as data rather than as a relation name — the difference is that an EAV table cannot have per-attribute types or constraints, while a 6NF schema with one relation per attribute can.

Temporal data. A transaction-time schema records the valid interval of each attribute separately, because attributes change at different times. Storing a salary and a department in one relation forces their intervals to be aligned, which duplicates whichever attribute did not change. One relation per attribute, each carrying its own interval, avoids the duplication. This is the shape that temporal designs converge on, and it is the strongest practical argument for the form.

Generic modelling. Treating every fact as a key-attribute-value triple, with the attribute named by a foreign key into a metadata relation, is 6NF in structure. It suits schemas whose attribute set is not known in advance, at the cost of enforcing constraints through metadata rather than through column definitions.

Many optional, independently constrained attributes. Regulatory or clinical schemas with hundreds of fields, most absent for most records, each with its own validation rule: the 6NF form lets each rule attach to its own relation.

Outside those cases the join cost dominates. An order-processing schema with twenty attributes per order, all usually present and read together, is a poor candidate: the attributes are correlated in their presence, so the nulls the decomposition would save are few, while the join cost is paid on every read.

The relationship to the earlier normal forms

6NF does imply 5NF, so the implication runs in the same direction as the rest of the ladder. What differs is how the condition is checked. Third normal form, BCNF, 4NF, and 5NF each ask whether the dependencies a relation satisfies are of an allowed shape, which requires knowing the dependencies. Sixth normal form asks a question about the heading alone, and the answer is visible without any dependency reasoning.

That difference has a practical consequence for tooling. Checking 3NF, BCNF, 4NF, or 5NF requires enumerating the dependencies and testing implication, which is where the complexity ladder of inclusion dependencies and the undecidability that follows them applies. Checking 6NF is a syntactic test: does any relation have more than one non-key attribute? It is linear in the schema size and requires no dependency reasoning.

The price of that syntactic simplicity is the loss of declarative constraints that span attributes. In 3NF, a functional dependency X → Y is enforced by a key or a constraint on one relation. In 6NF, any constraint relating two non-key attributes spans relations and cannot be declared as a CHECK. The decomposition is lossless and preserves keys, but it does not preserve constraints that reach across attributes, and those become triggers or application invariants.

The 3NF synthesis algorithm described in Boyce-Codd normal form and the cost of losing a dependency preserves every functional dependency by construction. 6NF preserves only the key-attribute associations, one per relation. That is one extreme of a spectrum whose other end is the single universal relation, which preserves every constraint and suffers every anomaly. Neither end is a design; useful schemas sit in between, and the position is chosen per relation by asking which constraints must be enforced declaratively and which queries must not pay for joins.

A worked comparison

Take an entity with key id and attributes name, status, opened_on, and closed_on, where the domain rule is that status = 'closed' implies closed_on is present and no earlier than opened_on.

In a single relation, the rule is a row-level CHECK that references three columns: status, closed_on, and opened_on. It is declarative, it is evaluated on every insert and update, and it cannot be bypassed by any write path that goes through the engine. The decomposition is not what prevents the inconsistent state; the constraint is.

In 6NF, status, closed_on, and opened_on sit in three relations, so the rule spans relations and cannot be a CHECK. It becomes a trigger over three tables, a deferred assertion where the system supports one, or an application invariant. A row in entity_status with value closed and no row in entity_closed_on is representable, so the inconsistent state is not prevented by the shape of the schema — only by whatever mechanism replaced the CHECK. The two designs enforce different things: the single relation enforces the rule declaratively at no join cost, and the 6NF schema costs a join per attribute and enforces nothing beyond key uniqueness.

That comparison is the honest summary of sixth normal form. It removes anomalies by removing the structure in which they can occur, and it removes constraints by the same operation. It is the right design when the anomalies are real — when attributes are sparse, independently timed, or unknown at design time — and the wrong one when the constraints are the point.