Re: Are there performance advantages in storing bulky field in separate table?

On Wed, Apr 08, 2009 at 05:06:44PM -0400, Robert Treat wrote:
> A slightly longer answer would be that, as a general rule, attributes
> of your relations that only apply to 1% of the rows are better
> represented as a one to N relationship using a second table.

Have you tried to maintain a non-trivial schema that does this? I'd be
interested to know how it works because I've only tried to work with
small examples that do this and it gets difficult to maintain very
quickly.

> For a longer answer, see
> http://www.databasedesign-resource.com/null-values-in-a-database.html
> or http://www.dbazine.com/ofinterest/oi-articles/pascal27

Both of those articles seem to be written by people who struggle with,
or have incomplete mental models of, the semantics of NULL values. The
second also appears to be designed to sell a book so is of course going
to be presenting biased viewpoints.

How would outer joins work without some concept of a missing value.
Once you allow these missing values as the result of an outer join
you would be deliberately introducing limits if you couldn't also save
these values back into tables. I would say that defaulting columns to
allowing NULLs was a mistake though.

I'd be happy without NULLs in databases if there was some other way
to handle missing values. Parametric polymorphism and some sort of
option[1] or Maybe[2] type is what springs to mind for me. NULL would
be represented as NONE or Nothing respectively and non-NULL values as
(SOME v) or (Just v).

--
Sam http://samason.me.uk/

[1] http://www.standardml.org/Basis/option.html
[2] http://www.haskell.org/onlinereport/maybe.html