Re: So what's null then if it's not nothing?

From: Hugo Kornelis <hugo_at_pe_NO_rFact.in_SPAM_fo>
Date: Thu, 08 Dec 2005 23:25:34 +0100
Message-ID: <tsbhp1dl9592eqd6b3j78boa18dlk7veds_at_4ax.com>


On Thu, 8 Dec 2005 16:48:47 +0100, Jon Heggland wrote:

>> Instead of
>> storing precomputed truth values/judgements, one should arguably
>> rather store actual facts.
>
>I do. A person's opinion on whether Oslo is the capital of Honduras is a
>fact just like the person's last name is, if it is opinions we are
>interested in. I don't care what the capital actually *is*, at least not
>enough to put it in the database.
>
>> You suggest to store:
>>
>> (person, opinion, veracity).
>
>No, actually. I meant (person, opinion, ...) (if it were a binary
>relation we wouldn't need 'missing' NULLS), where the domain of
>'opinion' is { TRUE, UNKNOWN, FALSE }.
>
>> I suggest to store (person, opinion) where opinion is a set {opinion1,
>> opinion2,..., UNKNOWN} and then derive veracity.
>
>Of course it can be handled differently. I just don't see why my initial
>design should be disallowed.

Hi Jon,

It should be disallowed because you store something that can be calculated from base data instead of storing the base data.

Looking back in this monster thread, the start of this example was this:

"Say we have a table of persons. We use a "logical value" column (3VL logic) to store each person's opinion on whether capital of Honduras is Oslo. Some persons say that it is, so we set their column to TRUE. Some say that it isn't, so we set it to FALSE. Some say they don't know, so we set it to UNKNOWN. Some persons haven't been asked, so we set their column to NULL. If we use NULL for UNKNOWN, we can't differentiate between persons who don't know, and persons who haven't been asked."

But if you ask someone for his or her opnion on whether the capital of Honduras is Oslo, the answer will not be a "logical value". It will be "yes", "no", "blimey, how should I know", "ummm, lemme think", "I though not", or whatever. It makes sense to restrict this somewhat - use a multiple choice form when interviewing the persons and use a corresponding check constraint in the database. If for whatever reason the question is not answered, don't register an answer in the DB. In SQL DDL terms, the column is

 OpinionOnOsloAsCapitolOfHonduras CHAR(10)

                CHECK ('Yes', 'No', 'Don''t know')
                DEFAULT NULL

The boolean that you are proposing could be derived from this. Either you call if "KnowsGeography" and have a derivation rule that sets it to true if OpinionOnOsloAsCapitolOfHonduras = 'No', unknown if it's NULL and false if it's any other value, or you call it "SucksAtGeography" and set it to true if OpinionOnOsloAsCapitolOfHonduras = 'Yes', false if it's either 'No' or 'Don''t know' and unknown if it's NULL.

But since it's derived, you'd never store it in the database.

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)
Received on Thu Dec 08 2005 - 23:25:34 CET

Original text of this message