Re: 3vl 2vl and NULL

On 5 Dec 2005 18:17:49 -0800, michael_at_preece.net wrote:

>
>Hugo Kornelis wrote:
>
>> On 4 Dec 2005 17:57:01 -0800, michael_at_preece.net wrote:
>>
>> (snip)
>> >OK. There is one fundamental problem with this. Some of those NULLs in
>> >those tuples might represent that we know something is empty - so it
>> >has to be treated as a known value. All NULLs are not created equal.
>>
>> <sigh>
>>
>> Hi Mike,
>>
>> No. If something is known to be empty, it's not stored as NULL. At least
>> not in SQL databases (which this thread is about, IIRC).
>>
>> If a comment is empty, iut's stored as the empty string (''). If a
>> container is empty, it's contents is stored as zero gallons (0). Dates
>> can't be empty. (Days can be, though - that's stored as an absence of
>> rows in the Events table).
>>
>> Best, Hugo
>
>What type of liquid is stored in an empty mug? Is it "unknown"? No - it
>is not an unknown type of liquid. It is no liquid. The value of the
>"liquid" column for the row would be empty. It would be NULL.
>
>Super,
>Mike.

Hi Mike,

As JOG already points out, the contents of a mug should be in a seperate subtype table for non-empty mugs. Mugs have a row in the NonEmptyMugs table iff Mugs.Contents > 0 cc.

But let's suppose that you still prefer to keep the Liquid column in Mugs. Let's say, for performance reasons. Or because your boss forces you at gunpoint. Whatever. In that case, the Mugs.Liquid column would be NULL (not empty) if the mug is empty. This still means nothing more than "no value here". As I already said before: if you feel the need to know *why* there's no value in Mugs.Liquid, then you'll have to use an extra column for it. And hey! in this case there already is such a column. Look here: Mugs.Contents == 0 cc. Now we know *why* there was no value in Mugs.Liquid for this mug. Not by looking at the NULL, but by looking at another column and infering a conclusion that we (as domain experts) can draw from the data present, but that the database itself could never conclude.

Best, Hugo

-- 

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