Re: ? How do you insert data into Nested Ref Tables

From: netguru <netguru_at_swlink.net>
Date: Mon, 26 Nov 2001 21:45:20 -0700
Message-ID: <9tv61805lo@enews2.newsguy.com>

Ganesh,

The example that I gave is very simple. The 'real' database is more complex.
The 'table' that I want to reference will be about 300 tuples, one for each software product that we support.

We have 3000 customers world wide (ie 3000 tuples/records). A customer may use one or more software that we support.

The idea was that instead of having the table 'inserted' into the customer relation as a 'column', that to avoid redundancy (BCNF) that to use the REF version of the nested table.

What this gives is a 'table of references' (16 bytes per reference I think) in the customer record, that point to the single software relation/table. Therefore there is only one copy of the software data, not 100's or 1000's.

This is not standard database, I know, but it is faster, and avoids data redunancy.

eg.

Customer Table / Relation                                         software
Relation
---------------------------                                         --------

---

cust # cust details cust software

---

 1         aaaaaa            sw 1 ref    ---------+------->  sw 1 record
                                   sw 2 ref                  |
sw 2 record
                                   sw 3 ref                  |
sw 3 record
-----------------------------------             |               sw 4 record
2          bbbbbb           sw 2 ref                  |               sw 5
record
                                   sw 4 ref                  |
-----------------------------------             |
3          cccccc            sw 1 ref  ----------+
                                  sw 3 ref
                                  sw 5 ref

Now the only problem I have is how to INSERT the data into the Software relation via the Customer record, or if there is some MAP type of command that will take an existing Software table and allow the mapping of the references in the customer Table.

Oracle documentation shows how to access data set up this way, but does not show how to actually add or create the data in the first place (or at least it has escaped my attention).

I hope that this clarifies the problem and that someone may be able to help.

thanks in advance
NetGuru

"Ganesh Raja" <ganesh_at_gtfs-gulf.com> wrote in message news:a8aed4.0111242259.40dfd7f9_at_posting.google.com...
> Hi,

>
> Sorry that i did notsee u'r syntax fro the Create Type as table of command
there.

>

> Why do u need a ref Object there.

>

> A Normal Table of objects will do to Implement a nested Relation.

>

> Regards,
> Ganesh R

> Received on Mon Nov 26 2001 - 22:45:20 CST