List:General Discussion« Previous MessageNext Message »
From:Bob Hall Date:September 16 2003 3:21am
Subject:Re: Does Null == ""?
View as plain text  
On Mon, Sep 15, 2003 at 09:53:11PM -0400, Bruce Feist wrote:
> Bob Hall wrote:
> 
> >The meaning of NULL is defined in the SQL specification; it means 
> >"not known" or "not applicable". 
> >
> Which is just about as useful as not defining it, actually.  The 

That's not true. Having a univerally understood designation for 
"not known" or "not applicable" is extremely useful. It would have 
been better if the original SQL committee had specified a designation 
for each, but even the combined designation is useful.

> vagueness is the cause of a great many program bugs when database 
> designers don't specify what NULL means for a given field.  To give a 
> hypothetical example:

This is a really bad example.
 
> The application is payroll/personnel.  A programmer is tasked with 
> creating forms for data entry on new employees, including supervisor.  
> If the user doesn't enter a new employee's supervisor, the application 
> accepts it, figuring that it is not yet known, and stores NULL for the 
> field ("not known" use of NULL).

I've worked with payroll databases. Usually, the business rules 
forbid putting the employee into the database if certain data is 
missing. The employee literally doesn't get a paycheck until this data 
has been supplied. In other cases, new employees go into a preliminary 
table until the missing information is supplied. 
 
> Meanwhile, a payroll programmer has been tasked with writing an 
> application to give the CEO a huge bonus and stock options.  To figure 
> out which employee is the CEO, the application looks for the employee 
> with NULL for supervisor ("not applicable" use of NULL).

This database would be impossible to maintain if the company were more 
than a few hundred employees. A better solution is to assign the employee 
to a section and assign a supervisor to the section. That way, you don't 
have to change each employee's record each time the supervisor changes.
 
> Suddenly, a large number of new hires are fabulously wealthy.  Who 
> screwed up?  Answer: the DB designer who didn't specify what NULL meant.

The meaning of NULL is already specified. The fault lies in the 
design of the database, not the definition of NULL. If the database 
were designed this way, then the people updating it would have to 
take responsibility for ensuring that a change that applied to only 
one person wasn't accidentally applied to someone else. Which makes it 
even clearer that the database is badly designed.
 
> >A zero-length string has no defined meaning. I've done maintenance on 
> >databases that contained zero-length strings, and they were nightmares.
> >I can't think of any reason why you would use a zero-length string in 
> >a database.
> > 
> >
> Because you know that a given person has no middle name?
> To represent "no value", as differentiated from "not known"?

If a person has no middle name, then the middle name field is "not
applicable", i.e. NULL. If an attribute is known to have no value, 
then you can't apply it to the entity.

Bob Hall
Thread
Does NULL == ""?Randy Chrismon15 Sep
  • Re: Does NULL == ""?Bruce Feist15 Sep
  • RE: Does NULL == ""?Mark Richards15 Sep
  • Re: Does NULL == ""?Brent Baisley15 Sep
    • Re: Does NULL == ""?Keith C. Ivey15 Sep
  • Re: Does NULL == ""?Paul DuBois15 Sep
Re: Does NULL == ""?Alec.Cawley15 Sep
  • RE: Does NULL == ""?Mark Richards15 Sep
Re: Does Null == ""?Randy Chrismon15 Sep
  • RE: Does Null == ""?Mark Richards15 Sep
  • Re: Does Null == ""?Bob Hall16 Sep
    • Re: Does Null == ""?Bruce Feist16 Sep
      • Re: Does Null == ""?Dan Nelson16 Sep
        • Re: Does Null == ""?Bruce Feist16 Sep
          • Re: Does Null == ""?Bob Hall16 Sep
        • Re: Does Null == ""?Bob Hall16 Sep
      • RE: Does Null == ""?Jon Frisby16 Sep
        • Re: Does Null == ""?Bruce Feist16 Sep
          • RE: Does Null == ""?Jon Frisby16 Sep
            • Re: Does Null == ""?Bruce Feist16 Sep
              • RE: Does Null == ""?Jon Frisby16 Sep
              • Re: Does Null == ""?Bob Hall17 Sep
        • Re: Does Null == ""?Bob Hall16 Sep
      • Re: Does Null == ""?Bob Hall16 Sep
    • Re: Does Null == ""?Bob Hall17 Sep
      • Re: Does Null == ""?Bruce Feist18 Sep
        • Re: Does Null == ""?Bob Hall18 Sep
  • Re: Does Null == ""?Haydies18 Sep
    • Re: Does Null == ""?Bob Hall18 Sep