MySQL Lists are EOL. Please join:

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

>>The application is payroll/personnel.  A programmer is tasked with 
>>creating forms for data entry on new employees, including 
>>If the user doesn't enter a new employee's supervisor, the 
>>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.
>I would disagree here.  I'd place the blame squarely with the programmer
>who made an *assumption* about the meaning of NULL in the absence of
Even if I grant you that, the DB designer should have produced the 
documentation.  I'm willing to place the blame on both people; either 
one could have avoided the problem.  But, the DBD (data administrator, 
if you prefer) is more likely to have been *aware* of the problem in the 
first place; it's their job to understand the data and serve as a 
reference to it.  Even if the programmer caught the ambiguity, the DBD 
would be the person who needs to resolve it -- and who should have done 
so in the first place.  The programmer was probably unaware of the 
ambiguity, and assumed that the system worked the way they wanted it to 
and thought it should.

Heck, we can blame it on QA as well.  But it's still the DBD who needs 
to fix it.  And it seems to me that the logical primary target for the 
blame is the person who has to redo their work once the problem is 

>It's the responsibility of the programmer to understand
>the system he or she is writing code for, before running that code.  
When you think you understand the system, the tendency is to go no deeper.

>documentation can't be found, the programmer should have asked the DB
>designer.  If the DB designer was unavailable the programmer should have
>at LAST tested the assumption ("SELECT COUNT(*) FROM employee WHERE
>supervisor_id IS NULL" -- there's only one CEO, so if it returns a value
>>1 the assumption is definitely false, if it returned 0, the assumption
>is definitely false, and if it returned exactly 1, the assumption MAY be
>true) before mucking with data.
A good programmer would do that, certainly.  But again, in doing so, the 
programmer is working around the deficiencies of the DBD.

>>Because you know that a given person has no middle name?
>>To represent "no value", as differentiated from "not known"?
>That's an ugly way to make the distinction between A-mark and I-mark.
Why?  If it's documented, it's a reasonably intuitive, clear, and 
probably efficient way.

>In most situations, I'd move the relevant column(s) to a separate table,
>with a NULL-allowed column in that table and a FK reference back to the
>original table.  The absence of a row in this child table indicates
>I-mark, and the presence of a row with a NULL in the column indicates an
So in the example I gave you'd set up a separate middle name table, 
making the middle name in it NULL (indicating A-mark) and have lack of a 
row in the middle name table be the I-mark?  (I like the A/I-mark 
notation, by the way -- I hadn't seen it before.)  I don't see the 
advantage, although logically it would do the job.  It'd be nasty for 
performance and programmatic complexity, though, especially for a minor 
value such as middle name.

>Or alternatively you could just get a database that has two kinds of
>NULLs, specifically defined to represent the distinction you bring up.
>( )
Interesting!  I hadn't been aware of any DBMSs that did that.  I'm not 
sure that it's a good idea, actually, because I suspect that there are 
other shades in meaning that might cause bugs as well.  For instance, is 
"no value" really the same as "inapplicable" in all cases?  I have 
trouble finding an example of this; here's the best I can do -- a 
password system.  A password might be unknown to the system, it might be 
an empty string (the user needs to explictly enter the fact that the 
password is '' somehow), or there might be no password, indicating that 
the user doesn't even want to be prompted for one.  I get very 
suspicious of scenarios where there are more than two possibilities -- 
it suggests that there might be an unlimited, or at least a large, 
number, and that not all possibilities have been clearly identified.

Bruce Feist

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