At 12:37 AM +0200 6/4/07, PFC 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 ?
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.
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.
It is very context-dependent.
My take on the NULL philosophy is that NULL should indicate that no
data has been entered. If the data for the record is not applicable,
then it should have a zero length string, indicating that the field
has been considered by the user, and that a blank value is
appropriate. A NULL field on an entered record should indicate an
error condition, rather than that the field is not appropriate to the
context.
Thus, NULL fields on a completed record would mean either that they
were never presented to the user (thus, did not appear in the UI), or
an error condition.
The advantages to this is that, if enforced, a count of the non-null
records will show those operated on by a user, vs. those untouched by
a user.
-Owen
