Skip site navigation (1) Skip section navigation (2)

Re: Use/Abuse of Nulls

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>,"Christopher Browne" <cbbrowne(at)acm(dot)org>
Cc: <pgsql-advocacy(at)postgresql(dot)org>
Subject: Re: Use/Abuse of Nulls
Date: 2003-10-31 22:33:30
Message-ID: 200310311433.30825.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-advocacy
Folks,

First off, we should probably really be having this discussion on the SQL 
list.

Well, there are two seperate issues with NULLs:

1) The tri-value problem;
2) Abuse of normalization

1) Is the problem that NULLs were implemented in SQL89 to *strictly* mean 
"unknown" or "undefined"; that is, values that existed but were not available 
to the database.   Unfortunately, the ANSI committee ignored the need for a 
"Not Applicable" value despite the rather primitive support for fk 
relationships at the time.   This has resulted in people using NULL to 
represent *both* "unknown" and "not applicable", meaning that you can't tell 
what is actually meant by looking at the NULL.   We really should have had 
two values, UNKNOWN and IGNORE.
	Certainly, with text fields it's easy to enforce not-nullness and make the 
user select "Unknown" and "Ignore" as string values.  However, it's difficult 
to come up with similar values that work for numbers, dates, or network 
addresses.

2) Given that the above abuse of NULLs is already built into the SQL standard, 
DBAs feel free to further abuse NULLs.  For example, a couple of weeks ago a 
developer posted a performance problem to the PERFORM list.   As it turns 
out, he had a table with 635 columns, of which 75% were NULL for any given 
row (this, BTW, was the source of his performance problem).  While this sort 
of not normalized design is not required by the NULL standard, it is made 
available and many junior DBAs exploit it.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


In response to

pgsql-advocacy by date

Next:From: Christopher BrowneDate: 2003-10-31 23:21:37
Subject: Re: Use/Abuse of Nulls
Previous:From: Merlin MoncureDate: 2003-10-31 18:34:36
Subject: Re: Use/Abuse of Nulls

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group