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

On 23 Nov 2005 13:39:35 -0800, michael_at_preece.net wrote:

>
>Hugo Kornelis wrote:
>
>> On Wed, 23 Nov 2005 01:09:56 GMT, Frank Hamersley wrote:
>>
>> >Hugo Kornelis wrote:
>> >[..]
>> >>
>> >> Mind you, I don't *KNOW* it can, since I know nothing about Pick. But
>> >> the description of Pick's data structure that DonR wrote (message-ID
>> >> <1132350557.673092.92800_at_g49g2000cwa.googlegroups.com>) reminds me very
>> >> strongly of XML. And in XML, it's easy to simply remove a "field" from a
>> >> "row": in "<Person><Name>Hugo Kornelis</Name></Person>", there are no
>> >> tags for birthdate, which clearly indicates that the value for my
>> >> birthdate is absent in this piece of data.
>> >
>> >Is "<Person><Name>Hugo Kornelis</Name></Person>" the same as
>> >"<Person><Name>Hugo Kornelis</Name><Birthday></Birthday></Person>"?
>>
>> Hi Frank,
>>
>> I don't think so.
>
>I agree. They're not the same.
>
>> I the first XML string, there is no Birthday attribute
>> for me. It's missing. In SQL, this would be represented by NULL in the
>> table, or my omitting the row if the birthday were stored in a seperate
>> table.
>
>Omitting the row sounds right. Using NULL is certainly wrong. The thing
>doesn't exist.

Hi Mike,

Au contraire. Using NULL is right. NULL is a special value to mark the ansence of a value. The "thing" as you call it (my birthday - a value in a database that tracks information about me) is absent from the DB, so using a marker to denote it's absence is correct.

Omitting the row is only an option in some designs. If this is the only table that holds my name, then omitting the row would wipe the fact of my existance from your DB. If new privacy regulations force you to remove birthday unless some very specific conditions are met and this is the only table that holds your company's prospects, than I'd like to have some put options, please.

>Nor does <WhatYouDidLastNight/>. Using NULL for
>everything that doesn't exist would take up a considerable amount of
>storage space - like the size of the known universe and more.

If the model included a WhatYouDidLastNight column, a NULL would have to go in that column too. Such a column would only be included if this information was considered relevant during design. Projects that try to gather enough information to fill the known universe typically have a time to completion that surpasses the age of the universe.

>
>> In Pick, this would (as I understand it) be stored by omitting
>> the cell for Birthday.
>>
>
>In the first example, yes.
>
>> The second XML string lists a blank birthday for me. That can't be
>> stored in SQL, as it's strong typing prevents a date from being blank.
>
>It should be represented by NULL. NULL is, after all, regardless of
>data type.

NULL is regardless of data type, but marks the absence of a value. In the second XML string, there is a Birthday tag, so the Bitrthday value is not absent. It's blank.

How a blank birthday should be interpreted, I don't know. I am glad that SQL just throws an error if I try to store a non-date in a date column.

>
>> The Pick data model appears to accept this (unless the DB engine checks
>> before inserting the data), as does XML.
>
>Correct.
>
>>
>> Best, Hugo
>> --
>
>Regards
>Mike.

Best, Hugo

-- 

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