Re: NULLS and User Input WAS Re: multimaster

On 6/3/07, PFC <lists(at)peufeu(dot)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