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

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).

> *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.

> 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.

Paul. Received on Mon Feb 07 2005 - 21:16:56 CET