----- Original Message -----
From: "Martijn Tonies" <m.tonies@stripped>
To: <mysql@stripped>
Sent: Tuesday, March 14, 2006 1:28 PM
Subject: Re: Accountability with MySQL
> >Should you have a flag for the status "movement complete" ? I would say
>>yes but instead of a simple checkbox, you could store a date value. That
>>gives you two pieces of information
>>
>>a) if the date is null then the movement is not complete.
>>b) if the date is NOT null then the movement is complete and you know when
>>it finished.
>
> There, Shawn and I disagree :-)
>
> In case of a very simple database, one could use NULL
> as a "flag" or "signal". But in general, I would avoid
> storing NULLs.
>
> Basics of database design: store what you know.
>
> Given that NULLs basically means the absence of a value
> (eg: unknown), you shouldn't be storing NULLs.
>
Nonsense!!
That's simply wrong. A null means "unknown or not applicable" and is a
perfectly valid value to use in many, many situations. Two classic examples:
1. You have a table containing test scores. Some students get every question
on a given test wrong. Some students don't write the test at all, perhaps
because they were sick. In this scenario, I think the correct thing to store
is 0 for the students who got every question wrong and NULL for the students
who didn't write the test.
2. You have a table containing employee records. One of the columns is
"termination date". What value do you store for a new employee's termination
date? Well, if they are a contractor on a fixed length contract, you could
calculate the date the contract ends; fair enough. If they are a permanent
employee and your area has mandatory retirement, you could calculate the
date they turn 65 (or whatever) and use that. But what if they are a
permanent employee and you don't have mandatory retirement? I would store a
NULL to mean "I don't know right now". Then, if and when they gave notice
that they were leaving, I would change the termination date from NULL to
their last day of work.
--
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.2/280 - Release Date: 13/03/2006
