Re: Why Varying Arrasy and Nested Tables?

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 Received on Wed Oct 09 2002 - 20:35:20 CEST