Re: Why Varying Arrasy and Nested Tables?
Date: 9 Oct 2002 17:38:53 -0700
Message-ID: <548b9514.0210091638.60056428_at_posting.google.com>
Russell Wolfe <wolfer_at_peak.org> wrote in message news:<d2t8qu46e58nvf4444j389kdtp4veo31ms_at_4ax.com>...
> On 7 Oct 2002 07:44:16 -0700, rajXesh_at_hotmail.com (RK) wrote:
>
> >Russell Wolfe <wolfer_at_peak.org> wrote in message news:<64mupugprsd7mq1d6a9ns0bae9u3kada79_at_4ax.com>...
> >> I've worked with Oracle extensively since version 3 in 1984. In all
> >> that time Oracle preached the glories of normalized data. I'm a
> >> believer. The relational model has held up very well for many years,
> >> and seems very robust to me.
> >>
> >> Then comes Oracle 8i and 9i with Nested Tables and Varying Arrays.
> >> Aren't Varying Arrays-presumably a Good Thing, since Oracle has
> >> them-the same thing as repeating fields, which Oracle used to condemn
> >> as a Bad Thing? Didn't we just de-normalize our data? And aren't
> >> Nested Tables just a more complex type of Varying Array?
> >>
> >> Both of these things add significnat complexity, it seems to me, to an
> >> already numbingly complex product. What do we gain with the addition
> >> of these things?
> >>
> >> Can anybody speak to this? Or point me toward a discussion of the
> >> pros and cons?
> >>
> >> tnx
> >> rww
> >> Russ Wolfe
> >> wolfer_at_peak.org
> >
> >
> >
> >Russ,
> >
> >
> >Nested tables and Varrays are a good thing because they are a new data
> >type. Most new data types and operators add functionality, flexibility
> >and readability to the language.
> >
> >For e.g. if '+' and '-' are basic operators, '*' and '/' which are a
> >extension of '+' and '-' are a good thing.
> >
> >Similarly NUMBER, CHAR, DATE, VARCHAR2 can be considered the basic
> >datatypes but %TYPE or %ROWTYPE which are not your basic datatypes are
> >still very useful
> >
> >Coming to Index by tables, Nested tables and Varays, they are
> >certainly very useful to have in PL/SQL as they offer a new
> >'Collection' datatype that was not present before.
> >
> >
> ><FROM http://www.utexas.edu/cc/database/datamodeling/rm/rm7.html>
> >
> >The goal of normalization is to create a set of relational tables that
> >are free of redundant data and that can be consistently and correctly
> >modified. This means that all tables in a relational database should
> >be in the third normal form (3NF). A relational table is in 3NF if and
> >only if all non-key columns are (a) mutually independent and (b) fully
> >dependent upon the primary key.
> >
> ></FROM>
> >
> >Nested tables and Varays add to the complexity (for now, because it is
> >a new concept) of programmatically storing and retrieving data.
> >
> >
> >Repeating fields are different from the Varrays. Consider for example
> >the table structure in this link
> >http://www.utexas.edu/cc/database/datamodeling/rm/rm7.html
> >
> >The 1NF contains repeating fields. The subsequent 3NF for 'supplier
> >and parts' can be rewritten as
> >
> >type parts_rec is object (
> >part# number
> >qty number
> >)
> >
> >type parts_table is table of parts_rec
> >
> >and the table is
> >
> >s# number
> >parts parts_table
> >
> >
> >As I was typing this, I just realized that we can have duplicate part#
> >in the parts table. So I have to agree with you in saying that Varrays
> >and Nested tables can in fact de-normalize the data.
> >
> >So what I am saying (for now until someone corrects me) is that
> >Nested tables and Varays are great in PL/SQL but bad in the database.
> >
> >
> >-- rajXesh
>
> rajXesh,
>
> I don't entirely agree with your first assertion. A new datatype may
> add flexibility, but readability? I'm not so sure.
>
> I also think Varrays and Nested Tables are more than new datatypes,
> they're a new way to design databases. Consider a simple
> master-detail relationship. In 3NF we have a master table, such as
> PurchaseOrder, and a detail table, like POLineItem. They join on
> PONumber. With nested tables, I can (I think, I've never done this)
> nest POLineItem into PurchaseOrder.
>
> I'm struggling with the pros and cons of this design choice. Do you
> have a real-world example of how a Varray or Nested Table has improved
> a database design?
>
> Thanks for the response
> rww
> Russ Wolfe
> wolfer_at_peak.org
Russ,
Varray or Nested Table are programming concepts and not at all helpful in the database design.
- rajXesh