Knobit of Data modelling · K-628 · Skill

Recognises a nullable foreign key on a reporting join path

What it means

A nullable foreign key that reports reach through with an inner join. Rows with a null drop out of every such report without an error: the physical form of the chasm trap (logical row 25).

How it is assessed

Flags the deficient case and passes the acceptable one. Deficient: `fact_account_opening.branch_key` null for accounts opened online; every report by branch loses them. `loan.collections_officer_id` null until an arrears case is assigned; loans newly in arrears are missing from the workload report Acceptable: In a dimensional model, an "Unknown" or "Not applicable" member and the foreign key `NOT NULL`; in a normalised model, the nullability kept, the reporting views using outer joins, and the logical model checked against logical row 25

Builds on

Recognises a chasm trapK-525 · Skill
Explains referential integrity and referential actionsK-653 · Knowledge
Explains the connection, fan and chasm trapsK-569 · Knowledge

Needed by

None