Re: Does Null == ""?

[Bruce Feist]

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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]