database design

Nicholas Leippe nick at byu.edu
Thu Jun 9 12:57:27 MDT 2005


On Thursday 09 June 2005 12:30 pm, Gabriel Gunderson wrote:

Agreed.

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 
is a practical shortcut to a much more laborious schema.

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.

I'm all for NULLs.

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:

> SELECT CONCAT_WS('', 'hello ', NULL, 'world');
hello world

That one is particularly nice for creating WHERE IN clauses:

> SELECT CONCAT_WS(',', 'foo', NULL, 'bar');
foo,bar

A more useful result in many cases than "foo,,bar" would be.

There are also, of course, foreign key constraints that can help where 
references are concerned such as ON DELETE SET NULL.



-- 
Respectfully,

Nicholas Leippe
Sales Team Automation, LLC
1335 West 1650 North, Suite C
Springville, UT  84663 +1 801.853.4090
http://www.salesteamautomation.com



More information about the PLUG mailing list