Re: NULL or 0 for a "FLAG_COLUMN"

From: IANAL_VISTA <IANAL_Vista_at_hotmail.com>
Date: Thu, 07 Sep 2006 02:19:48 GMT
Message-ID: <Xns9836C4A2215ECSunnySD@70.168.83.30>

"Martin T." <bilbothebagginsbab5_at_freenet.de> wrote in news:1157571842.244490.269310_at_m73g2000cwd.googlegroups.com:

> Brian Peasland wrote:

>> > Brian - are you stating that a) and b) implement different business
>> > rules??
>>
>> Absolutely! In a) you are allowed different values, but NULL is not
>> allowed. In b) you are allowed different values, including NULL.
>>
>> The NOT NULL constraint *requires* a value to be put into the column.
>> The absence of this constraint means that you are allowed to put NULLs
>> in the column.
>>
>> > The business rule is: "This column represents true or false, it must
>> > not be undefined".
>>
>> Then you must have two constraints...NOT NULL and CHECK IN (0,1).
>> Without those constraints, it is possible for you to violate your two
>> rules you stated above.
>>
>> > Both a) and b) allow the column to have exactly two distinct values,
>> > just the representation of the two distinct values is different. (NULL
>> > and 1 vs. 0 and 1)
>>
>> I get that, but NULL does not mean anything. That is the definition of
>> NULL. The value is *undefined*. You can choose to further extrapolate
>> that the presence of 1 means TRUE and the presence of NULL means FALSE,
>> but that would be a mistake in my opinion. Someone else in your company
>> comes along and they might not understand this, as it goes against the
>> accepted definition of NULL. You stated in your business rule "it must
>> not be undefined" yet you then want to use a value which by definition
>> means it is undefined? See where the confusion can take place later on
>> down the road?
>>
>>

>
> Brian - thanks. Seems I was not clear enough with the initial
> description.
>
> I think we agree that for normal cases the usage of NULL to represent
> anything except undefined or "not there" is a no-no :)
>
> My initial question was more geared towards the technical implications
> of a (NULL, 1) column vs. a (0, 1) column - but it seems my post did
> not state this clearly enough.
> (Anyways ... in the special case that triggered my initial post we have
> now a DATE column where NULL means "not set" and a valid DATE means
> "set" plus gives the info when it was set.)
>
> best,
> Martin
>
>

You should also keep in mind that NULLs can never be indexed; which could lead to performance issues. Received on Wed Sep 06 2006 - 21:19:48 CDT