Re: NULL or 0 for a "FLAG_COLUMN"

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 Received on Wed Sep 06 2006 - 14:44:02 CDT