Re: NULL or 0 for a "FLAG_COLUMN"

> 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?

Cheers! :)
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown