Re: "advanced" database design (long)

On Sun, 2008-02-10 at 03:08 -0500, Lew wrote:
> SunWuKung wrote:
> > I always thought that having nullable columns in a table is a Bad
> > Thing (http://technet.microsoft.com/en-us/library/ms191178.aspx) and
>
> Ridiculous.  The argument provided in that article is specious and likely SQL
> Server-specific.  NULLable columns should occur wherever your data model calls
> for them, typically when you want to have a marker for "unknown" data.  The
> advice in that article to move NULLable columns off to a separate table will
> actually cause worse, manual "special handling that increases the complexity
> of data operations" than the built-in and optimized handling the engine
> provides for NULLs.  You should ignore this terrible advice.

I disagree that it's ridiculous. There are good arguments for avoiding
NULLs, not the least of which is that they can lead to very unintuitive
results from queries due to 3VL.

I think the passage in question (from the above URL) is reasonably good
advice. They recommend vertical partitioning to avoid NULLs, and I think
that is a very good design strategy in many cases.

> > I think its much better to avoid it whenever you have the info but
> > when you don't you just have to use the EAV model.
>
> Also ridiculous.  You should never "have to use" the EAV so-called "model".

Agreed.

> question being described.  The difficulty of conceptualizing data structures
> as EAV is one of the big strikes against it.  The quoted citation evidences

Agreed. It has many other strikes as well: for instance, it's difficult
to form any kind of meaningful predicate for a relation in an EAV
design.

Regards,
    Jeff Davis