List:General Discussion« Previous MessageNext Message »
From:Bruce Feist Date:September 16 2003 2:33am
Subject:Re: Does Null == ""?
View as plain text  
Dan Nelson wrote:

>In the last episode (Sep 15), Bruce Feist said:
>  
>
>>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).
>>
>>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).
>>
>>Suddenly, a large number of new hires are fabulously wealthy.  Who
>>screwed up?  Answer: the DB designer who didn't specify what NULL
>>meant.
>>    
>>
>
>Your example has nothing to do with the vagueness of NULL though. 
>Replace NULL with "0" and you get the same result.
>
Only because '0' would be used as a "special" value -- you're using it 
to mean "unknown" or "inapplicable", instead of having its correct 
meaning of employee ID 0.  If you do so, you as the db designer are 
responsible for documenting the special meaning and making sure that the 
application developers know what you've done.  Sure, you can introduce 
vagueness by redefining certain values of a field to be special -- but 
if you stick to the natural meaning, that doesn't happen; integer and 
other values have precise and obvious natural meanings.  NULL does not.

>Of course, if I were the payroll programmer, I would simply select all
>employees WHERE emp.titleid = titles.id AND titles.name="CEO".
>  
>
Certainly that's a better way of doing it, given the option.  But, based 
on a business rule that only the CEO has no supervisor and on the 
assumption that a NULL supervisor field means that an employee has no 
supervisor, the application developer's code was correct also.  If 
something is left ambiguous, *someone* will make the choice you didn't 
expect.

Bruce Feist



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