21 messages in com.mysql.lists.mysqlRE: Does Null == ""?| From | Sent On | Attachments |
|---|---|---|
| Randy Chrismon | 15 Sep 2003 09:46 | |
| Mark Richards | 15 Sep 2003 10:02 | |
| Bob Hall | 15 Sep 2003 18:02 | |
| Bruce Feist | 15 Sep 2003 18:52 | |
| Dan Nelson | 15 Sep 2003 19:24 | |
| Jon Frisby | 15 Sep 2003 19:29 | |
| Bruce Feist | 15 Sep 2003 19:33 | |
| Bruce Feist | 15 Sep 2003 19:54 | |
| Bob Hall | 15 Sep 2003 20:20 | |
| Bob Hall | 15 Sep 2003 20:24 | |
| Bob Hall | 15 Sep 2003 20:45 | |
| Bob Hall | 15 Sep 2003 21:28 | |
| Jon Frisby | 16 Sep 2003 11:47 | |
| Bruce Feist | 16 Sep 2003 14:39 | |
| Jon Frisby | 16 Sep 2003 14:55 | |
| Bob Hall | 16 Sep 2003 17:31 | |
| Bob Hall | 17 Sep 2003 12:57 | |
| Bruce Feist | 17 Sep 2003 18:57 | |
| Bob Hall | 17 Sep 2003 21:02 | |
| Haydies | 18 Sep 2003 04:43 | |
| Bob Hall | 18 Sep 2003 12:04 |
| Subject: | RE: Does Null == ""? |
|---|---|
| From: | Jon Frisby (jfri...@yfdirect.com) |
| Date: | 09/15/2003 07:29:16 PM |
| List: | com.mysql.lists.mysql |
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