Re: How will schemas be affected by nested relations?

From: <>
Date: 31 Jul 2006 11:25:53 -0700
Message-ID: <>

Marshall wrote:
> How will schema design be affected by having the ability to use
> nested relations? I have an intuition that it might not be that
> much; that nesting is a *little* useful but not all that *much* useful.
> However, I am concerned that I don't have a normal form to
> inform design choices.
> When should relation schemas be nested?

I'd say, "never". I suppose it might be nice to see some syntactic sugar added to RDB's that allow for a "NEST JOIN", e.g.:

SELECT fName, emailAddress, nickName
FROM Person
LEFT NEST JOIN PersonEmail USING (personID) LEFT NEST JOIN NickName USING (personID) ;

...where each left nest join "folds" the right relation into the left relation...

> I have an idea that the answer might relate to when we use
> ON DELETE CASCADE. If a logical entity has no existence
> without the "enclosing" context of another entity, then it might
> be nested. Another consideration is whether it needs to
> be referred to directly.

The very existence of this quandry tells me that there's something fishy about the idea of using nested relations in the first place.

> For example, an invoice line item without an associated
> invoice doesn't make much sense. However, an invoice
> is probably something we want to keep even if for some
> reason we delete the associated customer. So I can see
> invoice line items as being a candidate for nesting, but
> not invoices.

Optional cascading deletes handle this quite nicely, in my opinion.

> However I would be much happier if I had a more formal
> treatment than this. Anyone have any references?
> Marshall
Received on Mon Jul 31 2006 - 20:25:53 CEST

Original text of this message