On 6/3/07, PFC <lists@xxxxxxxxxx> 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
