Recognises a nullable foreign key on a reporting join path
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).
Can AI do this?
Not tested yet
No AI benchmark covers this knobit yet. New results are announced in the Can AI Do the Job? newsletter.
What it looks like in practice
Expected result: The rows dropping out and the join to use
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
Also known as: Nullable foreign key on a join path (physical model)
Related knobits
Builds on
- K-525 Recognises a chasm trap
- K-653 Explains referential integrity and referential actions
- K-569 Explains the connection, fan and chasm traps
All 40 knobits of Design the physical data model.
Source: Review checklist 06-physical, row 28; Howe; C&B ("Problems with ER Models"); Codd 1979 §2.3