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

Paul wrote:
> lauri.pietarinen_at_atbusiness.com wrote:
> > The SQL to get all email-addresses and phone numbers
> > would be something like this:
> >
> > select ...
> > from PERSON P, EMAIL E, PHONE PH
> > where P.person_id = E.person_id and
> > P.person_id = PH.person_id
> >
> > The problems with this SQL is that
> >
> > 1) You would get 6 rows for TOM, 2
> > for each phonenumber and 3 for each
> > email address
>
> OK, but that's because the question you are asking is "show me all
> possible email-phone combinations for each person". Really you want
to
> be asking: "show me all emails for each person, and also show me all
> phone numbers" and I think there's no getting away from the fact that

> this is fundamentally two queries under the relational model (i.e.
using
> first-order propositions).

If we don't have nested relations...

>
> > *A* solution for 1) would be to nest
> > the emails and phone numbers, naturally
> > only in the result, not in the schema!
> >
> > Another solution would be to use
> > separate gueries for each set of
> > emails and phone numbers, but that
> > WOULD add to the network traffic.
>
> Unless you used database drivers that could handle sending multiple
> queries to the database at once. And getting the answer back as
multiple
> result sets.

Which you would have to glue together in the app. program "by hand"

>
> > We would of course need an API in the
> > programming language that can understad
> > this construct, and if we had an IDE
> > it would have to understand this format
> > and provide controls accordingly.
>
> OK so you're suggesting that a nested relation is only of use for
> phsically moving data from inside the database to outside - to some
> external application? I still think that this kind of thing is best
done
> outside of the relational model, maybe as part of the database
drivers.
> Keep the logical core as simple and elegant as possible and relegate
any
> possible performance-enhancing tweaks to the physical level.
>
> I can see how what you're saying could be useful at the physical
level
> but I think it's dangerous to let it creep in to the logical level.
>
> Maybe the application could use some kind of extended-SQL that had
> nested relations, but the database drivers convert it into
standard-SQL
> behind the scenes before speaking to the database. And then when the
> answer comes back (maybe as several tables), the drivers can convert
it
> into the form you're suggesting before passing it back to the
application.

Your points are valid and such "extensions" to the logical level would not come for free, i.e. it would add unfortunate complexity.

However, the driver-solution suffers from the problem that it is not deep enough in the engine so it cannot participate in the optimizing process.

Returning to my example you would have the following three queries:

select *
  from person
  where <lots of complex search criterias>

select *
  from person,email
  where <lots of complex search criterias>

select *
  from person,phone
  where <lots of complex search criterias>

• driver code here to merge-join these result sets

But this is not an issue that I feel very strongly about. It was just a thought and an example on how nested relations could be useful.

regards,
Lauri Pietarinen Received on Mon Feb 07 2005 - 22:54:14 CET