Recognises a sentinel value standing in for null

K-629 · Skill knobit · Activity: Design the physical data model · Competence: Data modelling

A real-looking value standing for "unknown" or "not applicable", or a null that means something specific and different in different tables. Sentinels pass every constraint and end up in totals and averages. A specific form of logical row 6 at the physical level.

Can AI do this?

AI does this well Most of the tested AI models master this. 10 of 10 models master it.

Mastered means a probability of at least 90% that the model has this knobit, estimated from its graded answers. “Not shown” means the answers do not settle it either way. How the models are tested.

What it looks like in practice

Expected result: The sentinel and the null or status column replacing it

How it is assessed: Flags the deficient case and passes the acceptable one. Deficient: account_opening_date = '1900-01-01' when unknown; branch_id = -1; open-ended validity as NULL in one table and 9999-12-31 in another Acceptable: NULL for unknown. Where a value is required, one project-wide convention recorded in config (open-ended valid_to), and a dimension's unknown member as a labelled row that every query sees, not a magic number in a measure

Also known as: Sentinel value in place of null, or null with a business meaning (physical model)

Related knobits

Builds on

• K-506 Recognises an attribute with several meanings
• K-655 Explains null semantics and three-valued logic

Needed for

• K-820 Recognises a default inventing a business fact

All 40 knobits of Design the physical data model.

Source: Review checklist 06-physical, row 19; Karwin, Fear of the Unknown; Codd 1979 §2.3