Anchor Modeling: sixth normal form, and what one table per column really means

Also called: 6NF modelling, anchors attributes ties and knots, the Anchor model

The most rigorous model on this list, and the one whose costs are the most immediate: everything it makes cheap to change, it makes expensive to read.

What it is

Anchor Modeling stores data in the smallest pieces that can be stored separately. An anchor is an identity and nothing else — a surrogate key that says “a customer exists”. Every property of that customer is its own attribute table. Every relationship is its own tie table. Shared fixed vocabularies — a status list, a country list — are knots.

The consequence is the headline: roughly one table per column. A customer with twenty properties becomes an anchor plus twenty attribute tables. That is not an exaggeration or a worst case; it is the design working as intended.

What it buys is that change is always additive. A new property is a new table. Nothing that already exists is altered, no migration runs, nothing that was loaded has to move. And because each attribute carries its own validity time, you get complete history at attribute level for free — not “this row changed” but “this customer’s credit limit changed on this date, and nothing else did”.

How the tables are actually shaped

The four constructs

CU_Customer            -- the anchor: an identity, nothing else
  CU_ID          INT

CU_NAM_Customer_Name   -- one attribute = one table
  CU_ID          INT       -- which customer
  CU_NAM_Name    NVARCHAR  -- the value
  ChangedAt      DATETIME2 -- when it became true  (historised)

CU_STA_Customer_Status -- a knotted attribute: value from a fixed list
  CU_ID, STA_ID, ChangedAt

CU_pla_OR_Order_placed -- a tie: a relationship, its own table
  CU_ID, OR_ID

The rule the whole model rests on

An anchor’s surrogate key is assigned once and never renumbered. Every attribute row and every end of every tie finds its anchor by joining a natural key back through the anchor’s identifying attribute, so a second load has to arrive at the same number as the first. Gaps in the sequence cost nothing; a number that moves costs everything — the model quietly files one customer under two identities.

History, and the trap in it

Every attribute read from one source row must be stamped with the same moment, including the attribute whose value is a date. Reassembling a readable row means joining the attributes on the anchor and that moment, so one attribute stamping “now” while its neighbours stamp the order date leaves nothing to join on.

A historised attribute also has to compare against the previous value within the same batch, not only against what is already stored. Checking only the stored value lets a first load write every reading it was given, duplicates included, and call that a history.

Nobody queries this by hand

Reassembling one customer means joining the anchor to every attribute table you want, each filtered to the version that was current at the moment you care about. Anchor Modeling’s own answer is that the views are part of the model — generated, not written — and that is the correct answer. A model with no reporting layer planned is a model nobody can use.

When it fits, and when it does not

This one has the clearest fit test of the eight: who is going to write the SQL, and how often will the model change?

Those two lists are Netune’s own judgement of this methodology, copied from the rule file it scores a real database with.

What it costs

The mistakes that are expensive

Building the model without building the views

The single most common way an Anchor project fails. The data is beautifully stored and completely unreadable, and the team that has to report on it writes twelve-join queries by hand until somebody proposes starting again. The reporting views are part of the deliverable, at the same time as the model, not afterwards.

Letting the identifying attribute be cut off

The attribute that holds the natural key has to exist before anything else can be loaded, because it is the only way back from a business key to the surrogate. Build it first, whatever order the source lists its columns in — a model whose identity attribute was left for later builds perfectly and can never be filled.

Choosing it for a stable, small model

Anchor’s entire advantage is absorbing change without migration. On a model that is not going to change much, you have paid the join cost and the table count for a benefit that never arrives.

How it compares

Anchor Modeling or Data Vault?

They solve the same problem with different granularity. A Data Vault satellite holds a group of attributes that change together; Anchor holds each one separately. That makes Anchor’s history finer and its join count higher. The vault also has far more industry tooling, training and hiring pool around it, which for most teams settles it.

Anchor Modeling or a star schema?

Opposite ends of every trade. A star is optimised for being read and pays for it when the model changes; Anchor is optimised for changing and pays for it every time it is read. Anywhere a real warehouse uses Anchor, there is a dimensional layer on top of it.

Questions people ask