Re: NULL values or not? - Mailing list pgsql-sql

Archibald,  

> So again, I have no problems with NULLS, I just would like someone to
> share when they use them contra when they don't.

Thank you for bringing a theory question onto the list!  We spend much
of our time on pgsql-sql focused on practical minutia.  Thus several of
the respondants who siezed on your hypothetical example rather than
answering the question.  I'd love to see some of the more experienced DB
theorists weigh in on this issue (Tom?  Joe C.?).

Relationally speaking, NULLs are a necessary evil.  You are right to
approach them with caution.  I cannot count the number of times one of
my database rescue attempts has involved eliminating, sub-tabling, or
consolidating NULLable columns.  

By coincidence, yesterday I posted this to PGSQL-NOVICE:

============================================================
> 3) Use of NULL> 
> It would facilitate entering data into TABLE clone if seq, qual... 
> were defined as NULL even though values for these columns will/does 
> exist. If I defined these columns as NULL I would not have to have 
> ALL of the data together at one time, but could enter it in steps. 
> Is this an ill conceived notion?
Well, this purpose is what NULL is for. Cases where data is unknown ornot yet available. That being said, allowing
NULLstakes data integrityout of the table design and moves it elsewhere in the software. If youallow NULLs for seq,
thenyou will need to create a data integrityreport that searches for old entries with NULL in the seq column.Otherwise,
yourisk having some required data never filled in.
 
Also, remember that you can't JOIN on a NULL value. For example, if youallow NULLs in, say clones.gb_id, then if you do
areport on clones
 
JOINgb_accessions, the clones who are missing GB will not show up with ablank GB, instead they will not show up at all!
Youcan get around thiswith OUTER JOINS, but OUTER JOINs are not dependable for multi-columnjoins. 
 
Instead, I recommend that everywhere it is possible, you have an actualvalue that indicates why the data has not been
filledin. FOr example,you could create a gb_accession with the ID of zero (0) (and accn_no,gi_no of 0 as well) which
wouldindicate (to you) "gb not run yet".This gives you more information than NULL (which could indicate a
 
numberof things: GB not run, GB lost, data error, program error, etc.), aswell as supporting JOINs cleanly. You could
evenhave more than onesuch value to indicate different reasons for missing info.
 
=====================================================

For a more in-depth discussion of NULLs and their problems and
workarounds, see Fabian Pascal's "Practical Issues in Database
Management", which has the better part of a chapter on the topic.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco