Re: NULLS and User Input WAS Re: multimaster

On 6/3/07, PFC <lists@peufeu.com> wrote:
>
> > Yeah, it is awful ;^)  However the existing system is equally awful
> > because there is no way to enter NULL!
>
> Consider this form :
>
> First name :    Edgar
> Middle name :   J.
> Last name :     Hoover
>
> Now, if someone has no middle name, like "John Smith", should we use NULL
> or "" for the middle name ?

"NMN"  for No Middle Name.

http://www.google.com/search?hl=en&q=data+standards+no+middle+name+NMN&btnG=Search

The hazard with doing stuff like that is some joker could name their
kid Billy NMN Simpson.  Or this

http://www.snopes.com/autos/law/noplate.asp

If the the "None" identifier can't be guaranteed to not conflict with
data, the best thing is a boolean for "None".

> NULL usually means "unknown" or "not applicable", so I believe we have to
> use the empty string here. It makes sense to be able to concatenate the
> three parts of the name, without having to put COALESCE() everywhere.
>

Null always means unknown.  N/A usually means Not Applicable.  I use
COALESCE once in a view and never again.

> Now consider this form :
>
> City    :
> State   :
> Country :
>
> If the user doesn't live in the US, "State" makes no sense, so it should
> be NULL, not the empty string. There is no unnamed state. Also, if the
> user does not enter his city name, this does not mean he lives in a city
> whose name is "". So NULL should be used, too.
>

There are states in other countries, but I get your meaning.  But if
someone doesn't enter their middle name, that doesn't mean their
parents named them Billy "" Simpson either, right?

I think there is an argument for filling fields with empty strings
where they are _known_ not to exist but they are _applicable_  but I
don't do it.  I prefer the consistency of NULL for absent data versus
WHERE (mname = '' OR mname IS NULL).  Again, the user failing to enter
it when presented an opportunity does not meet the "known not to
exist" test for me.

> It is very context-dependent.
>

Yeah, unless you are a stubborn old null zealot like me!

- Ian