On Mon, Sep 15, 2003 at 09:53:11PM -0400, Bruce Feist wrote:
> Bob Hall wrote:
>
> >The meaning of NULL is defined in the SQL specification; it means
> >"not known" or "not applicable".
> >
> Which is just about as useful as not defining it, actually. The
That's not true. Having a univerally understood designation for
"not known" or "not applicable" is extremely useful. It would have
been better if the original SQL committee had specified a designation
for each, but even the combined designation is useful.
> vagueness is the cause of a great many program bugs when database
> designers don't specify what NULL means for a given field. To give a
> hypothetical example:
This is a really bad example.
> 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).
I've worked with payroll databases. Usually, the business rules
forbid putting the employee into the database if certain data is
missing. The employee literally doesn't get a paycheck until this data
has been supplied. In other cases, new employees go into a preliminary
table until the missing information is supplied.
> 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).
This database would be impossible to maintain if the company were more
than a few hundred employees. A better solution is to assign the employee
to a section and assign a supervisor to the section. That way, you don't
have to change each employee's record each time the supervisor changes.
> Suddenly, a large number of new hires are fabulously wealthy. Who
> screwed up? Answer: the DB designer who didn't specify what NULL meant.
The meaning of NULL is already specified. The fault lies in the
design of the database, not the definition of NULL. If the database
were designed this way, then the people updating it would have to
take responsibility for ensuring that a change that applied to only
one person wasn't accidentally applied to someone else. Which makes it
even clearer that the database is badly designed.
> >A zero-length string has no defined meaning. I've done maintenance on
> >databases that contained zero-length strings, and they were nightmares.
> >I can't think of any reason why you would use a zero-length string in
> >a database.
> >
> >
> Because you know that a given person has no middle name?
> To represent "no value", as differentiated from "not known"?
If a person has no middle name, then the middle name field is "not
applicable", i.e. NULL. If an attribute is known to have no value,
then you can't apply it to the entity.
Bob Hall
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
