Re: Can we solve this -- NFNF and non-1NF at Loggerheads

From: <lauri.pietarinen_at_atbusiness.com>
Date: 7 Feb 2005 05:55:44 -0800
Message-ID: <1107784544.498876.251700_at_g14g2000cwa.googlegroups.com>


Paul wrote:
> > Well said. My suspicion is that there is little to gain in
> > actually storing relations with nested relations, i.e. designing
> > your database with nested tables.
> >
> > One possible use for nested tables/relations could be in providing
a
> > richer interface towards application programs and report
generators.
>
> Anything done using nested tables can be done using standard
relations
> though (can't it?) so I can't see where the advantage lies in using
the
> scheme below. If you just mean that the front-end application uses an

> interface lookng like the diagram below, well, it could do that
equally
> as well using the standard relational design behind the scenes.

You are correct, except for perhaps the eliminating of NULLs (if you think that NULLs are a bad idea). Nulls would be represented by empty sets. Representing outer joins is more natural since rows with no corresponding "child rows" could be handled the same as the ones with child rows. Also, if you have a "multiway" outer join things get pretty tricky. Say if the person is associated with n emails and m phone numbers, you will get n x m rows with the person-columns repeated. These have to be eliminated in the application program.

Another benefit is lower network traffic (which can also be achieved via compression, of course), because the "outer table columns" are not repeated on each row.

And further, it would enable a dynamic GUI generation, i.e. a GUI that is generated "on the fly"). And it would also be a "hint" to a report generator which can easily figure out the break point columns.

>
> In fact, could you maybe say that any design using "nested" relations

> can always be transformed into a totally equivalent design without
using
> RVAs, and vice versa? So in some some the two theories are isomorphic

> and really it's irrelevant which one you use? So Occam's Razor would
> suggest the simplest one.

I think the "multiway outer join" is a bit hard to get right. Granted that the outer join operator is not in the "pure" toolbox set, it has practical value.

>
> I'd imagine a RDBMS engine would want to unnest nested relations for
its
> physical implementation, because otherwise it's going to complicate
> things like indexing etc.

I have not found a use for stored nested relations. I would definitely not use them in base tables.

>
> Maybe it's because I'm used to thinking relationally,

Me too!

>but I just can't
> think of any examples where a RVA would be necessary or even just
useful.
>

Perhaps you might want to consider the examples I gave?

regards,
Lauri Pietarinen Received on Mon Feb 07 2005 - 14:55:44 CET

Original text of this message