Practical considerations of dealing with two meanings of NULLs

One simplistic method of trying to distinguish multiple meanings possibly associated with NULL is to associate particular non-NULL meanings with those values.

For example, I might want to store both "unknown" and "known, but not yet filled in by data entry clerk." I could associate NULL to one and a particular non-NULL value to the other, or non-NULL values to both.

If the data at issue are e.g. strings or something categorical, this is OK.
(The only problem I can think of is the case where (in the interface---I'm
using a webserver for that) I give users a selectbox of predetermined choices, and there's an associated textbox where they can put in "Other"
(non-predetermined values)---what's to stop them from overlooking the
predetermined choice "Unknown" and typing in "don't know" in the textbox?)

But if the data are numerical, it's messy. It's easy if the data are known to be nonnegative, because then one can reserve particular nonnegative values for this (e.g. "-1 means not filled in by user," "-2 means known"). But this method just seems ugly and kludgey to me.

Is there any clean method of dealing with this? Received on Wed Aug 08 2007 - 05:58:35 CDT