Re: 3vl 2vl and NULL

Hi Dawn,

Here's a quick reply to some of your points. I'll be without access to any computer for the next week, so don't expect to hear anything from me during that period!

On 22 Dec 2005 06:53:30 -0800, dawn wrote:

>
>Hugo Kornelis wrote:
>> On 15 Dec 2005 14:21:18 -0800, dawn wrote:
(snip)
>> Yes, of course. If I had never known about 3VL, I wouldn't miss the
>> extra control it gives. You can't miss what you're not aware of.
>
>OK, so 3VL it isn't essential. Does it provide better value for an
>organization than using a 2VL? I say "no" and you say "yes." It
>sounds like we each have experience with both and have arrived at a
>different conclusion.

Indeed, we have.

(snip)
>> >Yes, it was incorrect. In order to simply, we could say that we
>> >modeled the proposition:
>> >
>> >Uncle Henry's age is unknown
>> >
>> >with the database data of
>> >
>> >Uncle Henry has no age
>>
>> No, Dawn, we didn't model either of those propositions. We only modeled
>> a proposition that looks like <person> is <age> years old,
>
>That is a fine predicte. The real world proposition I have in my hand
>is "Uncle Henry's age is unknown." You may choose to model and include
>this proposition however you like, including by ignoring this it
>altogether.

If that proposition turns out to be important during information analysis, then it should have been modeled as well. That would result in two (mutually exclusive and probably combined mandatory) propositions: - <Person> is <age> years old, and
- <Person>'s age is unknown.

The latter is a unary prediucate, which NIAM handles just fine. SQL has more trouble with it - it either has to go in it's own table, or an extra column has to be added, changing the predicate to the semantically equivalent (but somewhat clumsy and unnatural) - <Person>'s age is <Y/N-indication> unknown.

Most SQL people would choose a domain like {'Y', 'N'}, make the proposition mandatory and define that the Person/age proposition may only be supplied if the Person/unknown proposition is 'N'. Most NIAM people would choose a domain of {'Y'} and leave the proposition optional and mutually exclusive with the Person/age proposition.

No NIAM person and no SQL person would (or rather: should) ever think of using just one proposition and choose to interpret NULL as "Age unknown". That would be storing two data points (age in years and age known/unknown status) in one column - a clear violation of 1NF.

(snip)
>Simplifying in this way seems to make the data highly useful and
>useable for developers and end-users alike.

Au contraire. If there is just one single occasion where a person's age is known but can't (for whatever reason) be stored in the DB, then your simplified model is toast.

(snip)
> I would think it would be clear to most
>with these last two statements that this provides a more satisfying
>human-computer interaction.

The human-computer interaction should be handled by the front end. The SQL query language is a developer's tool, not an end user's tool.

>
>> Real people would still communicate the fact that they can't give an
>> answer, and start blaming others. But this fictional DB component is
>> constrained to only return an integer to his manager, and this integer
>> should be an age in decades.
>
>I disagree. I prefer to be able to model a wider range of propositions
>that might be collected.

In real program code, a column is often used in an exppression, like (CurrentBalance - OpeningBalance). In order for this to work somewhat predictable, you need these column names to be replaced with numeric values at runtime.

Modeling the range of propositions to be collected is an information analysis task. This should be done before even deciding if SQL, Pick, hierarchic DB, network DB, flat file or whatever other data storage and retrieval technique shall be used.

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)