Posted by Hugo Kornelis on 11/01/05 00:35

On Mon, 31 Oct 2005 00:06:38 -0500, serge wrote:

>I learned that our developers have decided not to write any ASP code
>that verifies if a column is Null or not. That's why we don't leave any
>columns with NULL values.

Hi Serge,

In my eyes, there are now two possible options:

1. You put your foot down as DBA and insist that they start doing what
they should do. They obey (either because you convince them or because
management agrees with you) and start checking for NULL. Everyone is
happy and your company will prosper.

2. You put your foot down as DBA and insist that they start doing what
they should do. They don't obey (because they won't listen to you and
management doesn't support you). You hand in your resignation since you
can't take any responsibility for the integrity in the database. You get
a new job, and are happy. Your ex-company tumbles becasue in the end,
lack of integrity in the key database brought the company to it's knees.


(snip)
>> Also, are you aware that all these zeros and spaces will muck up you
>> aggregate values, and that they can impact the logic of your queries?
>
>Can you please elaborate on this? I would like to understand how it
>would impact the logic of queries?

Assume we're building a machine that can guess people's age by analyzing
their facial features. For a test run, we invite some test persons to
the lab, have the machine guess their age, then ask to reveal their real
age. The information is stored in the database for future anaysis.
Unfortunately, test person Kate never showed up. The machine produced an
error code instead of an age when examining Karl. And Lindy refused to
reveal her real age after the machine had made it's guess. For
completeness' sake, we do store the information we have about Kate, Karl
and Lindy; ecause our ASP developers never check for NULL, we store the
unknown ages as 0.

This is how our table looks like (use a fixed font to read):

Person | Sex | GuessedAge | RealAge
--------+-----+------------+---------
John | M | 45 | 45
Gill | F | 22 | 18
Kate | F | 0 | 0
Karl | M | 0 | 56
Lindy | F | 37 | 0
Pete | M | 28 | 26

Now write a query to find all persons where the machine guessed exactly
right:
SELECT Person
FROM TheTable
WHERE GuessedAge = RealAge
Sounds logical - but will also return Kate! The logic of the query is
impacted by the use of 0 instead of NULL.

Also, write a query to find all persons where the machine was off by
more than 3 years:
SELECT Person
FROM TheTable
WHERE ABS(GuessedAge - RealAge) > 3
Sounds logical - but will also return Karl and Lindy! The logic of the
query is impacted by the use of 0 instead of NULL.

Now, write a query to find the youngest test person (based on real age,
not on guesses) and the average guessed age:
SELECT MIN(RealAge), AVG(GuessedAge)
FROM TheTable
Will return 0 for lowest real age and 22 for average guessed age, where
I would expect 18 for lowest real age and 33 for average guessed age.
The results of the aggregate functions are mucked up by the use of 0
instead of NULL.


>> Finally - how will you handle datetime variables? Neither 0 nor ' ' can
>> be stored in them!
>
>I see we're setting them all to "getdate()".

AAARGGGHHHH That is even worse. Setting them to a value that won't
normally be used would at least give you a small chance to even get any
useful information from your data. "Well, sir - either the applicant is
just two days old, or we entered his information two days ago but didn't
know his birthday at that time".

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]