Re: Nested Tables?

In article <95f3jd$3i$1_at_spiney.sierra.com>,   "Steve McDaniels" <steve.mcdaniels_at_sierra.com> wrote:
> Nested Tables:
>
> Assume you have a db which holds consumer info, such as
> name, address, telephone, email address, and some product info.
>
> A real simple example would be for handling of multiple telephone
 numbers.
>
> That is, you might have a name (probably first, middle, last),
> an address (like address1, address2, city, state, postal code [for
 U.S.
> consumers]),
> and telephone(s), perhaps a work phone, daytime phone, nighttime
 phone, etc.
>
> One might wish to store telephones as part of the name record using a
> "sub-table" of
> telephone numbers [and associated "type" as work, home, etc.]
>
> <jdorlon_at_my-deja.com> wrote in message
 news:95ek4v$2ic$1_at_nnrp1.deja.com...
> > Can anyone describe a scenario of when nested tables would be
 desired?
> > They just seem so complicated to me that they are not worth the
> > effort! Am I missing something?
> >
> > Thanks!
> >
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
>

Steve has a good example here. I work on some databases with several nested tables. Here are some general questions we ask ourselves anytime we're thinking of using one:

1 - Would the data have been placed in a child table if there were no such thing as a nested table? (I know, seems obvious, but its a good way to think about it).
2 - Will I want to get at the nested data 90% or more of the time when I access the parent row?
3 - Is the nature of the data such that I am all but 100% sure that I will never want to have a child relationship on any part of the nested data?

The answers to all of these questions must be Yes in order to consider the use of a nested table.

There is some great info on nested tables in Jonathon Lewis's "Practical Oracle 8i" book (I seem to be plugging this book alot). He goes into several of the features of nested tables for which documentation is slim to none.

HTH. Matt.

Sent via Deja.com
http://www.deja.com/ Received on Mon Feb 05 2001 - 07:41:48 CST