> For context, NULL/missing was a type. N/A was a type (i.e. a question irrelevant to someone). I can't remember the 3rd, and of course not the additional (4th or 5th) type he was proposing
“Don’t know”: this has a value, but we do not know it could be one. Also, within N/A, one _could_ discriminate between “not available yet”, “will never be available”, “was available once, but was lost”, etc.
Depending on the domain, others could be
- “Can’t tell”: this has a value, but you are not allowed not know it (unlikely, as you likely also shouldn’t be allowed that the value exists)
- incomputable: this has a value, but it isn’t possible to know it
I think generic systems shouldn’t try to capture such domain specific things, but allow for implementing them. SQL shouldn’t even have null, but have enums and product types on top of which you could create it and functions supporting it.
This reminds me of the sixteen or so different flavors of null used in HL7! https://terminology.hl7.org/en/CodeSystem-v3-NullFlavor.html though in practice I've only seen a handful of these. Notably:
NI No Information - the default, it is unknown and why it is unknown is also unknown
UNK Unknown - the most common other than NI
ASKU Asked but Unknown
NASK Not Asked - we don't know because we didn't ask
MSK Masked - hidden due to privacy or legal etc
NA Not Applicable
NAV Not Available - for example patient might be unconscious or doctor might be unreachable, temporary
How is “not available yet” different from "Don't know"? How is incomputable different from “will never be available”?
> SQL shouldn’t even have null
But it needs to, because it needs to represent half of a result record not existing, i.e. on a LEFT JOIN.
"Not available yet", at least to me, carries the implication that it will, not just might, become available at some point. Probably soon enough that it's worth asking the question again later. Whereas "don't know" suggests that it's not worth asking the question again, because you're probably just going to get "don't know" as the answer. (At least, if "don't know" and "not available yet" are both presented as options).