database design

Roberto Mello rmello at fslc.usu.edu
Fri Jun 10 09:14:41 MDT 2005


On Thu, Jun 09, 2005 at 12:57:27PM -0600, Nicholas Leippe wrote:
> 
> The NULL debate is nearly as bad or worse than the vi vs emacs debates by the 
> academics.  IMO, those that argue for strictness to the relational model 
> haven't written a line of production code in their life.  In many cases NULL 

That's quite a claim. Can you back it up?

> is a practical shortcut to a much more laborious schema.
 
These shortcuts can usually be avoided, keeping things even simpler (no
need to check for nulls all the time) and faster.

> The 'proper' relational way is to have another table that contains a row or 
> not depending on the existence of the data.  Then, you join to that table, 
> and if there is no matching row, that is how you'd know rather than using 
> NULLs.  Just sticking in a NULL in the first table can avoid the need for 
> join entirely.  For a lot of data, this is IMO the best solution because it 
> KISS.

http://www.hughdarwen.freeola.com/TheThirdManifesto.web/Missing-info-without-nulls.pdf
 
> MySQL has a few nice functions that help make NULLs less painful.
> COERCE() accepts a list of arguments and returns the first that is not null.
> CONCAT_WS() can be used to concat a bunch of values, ignoring the null ones:

For the sake of information, the standard SQL equivalents of COERCE
(supported in PostgreSQL) is COALESCE. Not sure if there's an equivalent
for CONCAT_WS().

-Roberto

-- 
Oh my GOD! A Prompt!!! HELP!!!! A PROMPT !!!!! HEEEEELP !!!!!!!!!!!!!



More information about the PLUG mailing list