On 6/3/07, PFC <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
