database design

Nicholas Leippe nick at
Fri Jun 10 09:29:03 MDT 2005

On Friday 10 June 2005 09:14 am, Roberto Mello wrote:
> 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?

No, of course not.  It's obviously an over-generalization, and is probably
considered flame-bait.

> > 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.

Usually?  In the document below, even without the NULLs, you _still_ have to 
check for the 'unknown' type--which comes from no matching row in the joined 
table.  What's the difference in code complexity between having to check for 
an 'unknown' type (which no db that I know of does anyways) and having to 
check for a NULL?  In practice, it reduces to nearly the same code, just that 
one doesn't require an extra join.

> > 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.

Yes, I have read this, and it corroborates what I said nicely.  I have seen an 
extended version of this document that even has a sample implementation of a 
database that implements the Tutorial D language.  But, note page 19:


How Much of All That Can Be Done Today?

- Vertical decomposition: can be done in SQL
- Horizontal decomposition: can be done in SQL
- Primary and foreign keys: can be done in SQL
- Distributed keys: can be done in (awful) longhand, if at all
- Distributed foreign keys can be done in (awful) longhand, if at all
- Multiple assignment: cannot be done. (Specified in The Third Manifesto, but 
nobody seems to have noticed yet.)
- Recomposition query: can be done but likely to perform horribly.
Might be preferable to store PERS_INFO as a single table under the covers, so 
that the tables resulting from decomposition can be implemented as mappings 
to that. But current technology doesn’t give clean separation of physical 
storage from logical design.


And his parting remark:


Perhaps something for the next generation of software engineers to grapple 


Thus I stick to my guns: NULLs provide a practical solution to many data sets 
that would otherwise cause more complication if normalized further.

> 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().

Yes, my bad.  In MySQL it is COALESCE also.  I type that one wrong all the 


Nicholas Leippe
Sales Team Automation, LLC
1335 West 1650 North, Suite C
Springville, UT  84663 +1 801.853.4090

More information about the PLUG mailing list