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
