> 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.
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
documentation. It's the responsibility of the programmer to understand
the system he or she is writing code for, before running that code. If
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.
Granted that the DB designer had the opportunity to prevent this
particular misunderstanding by designing a schema that distinguishes
between A-mark (absence of information -- "not known") and I-mark
(inapplicability of information), but it's impossible to completely
idiot-proof any system. At some point, the users of the system -- in
this case the programmer -- have a responsibility to achieve a certain
minimum level of understanding before using that system.
> 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.
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
A-mark.
Or alternatively you could just get a database that has two kinds of
NULLs, specifically defined to represent the distinction you bring up.
( http://www.firstsql.com )
-JF
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
