troubleshooting Question

Oracle many-to-many

Avatar of Joey_hunny123
Joey_hunny123 asked on
Oracle Database
1 Comment1 Solution3879 ViewsLast Modified:
Many-to-many in Oracle.  

It appears as though I am over complicating things.  I have an ER diagram with a many-to-many link.  I'm using Oracle as an Object-Relational-DBMS.  I know I have to break the many-to-many relationship into two one-to-many relationships with an intermediate table.  The intermediate table then contains two columns - primary key from table A and primary key from table B (I believe).

However, I cannot work out how to get relationships between this new table (C) and the two original tables (A and B).  Are there any required??  I would have thought so, however http://www.oracle.com/technology/products/ias/toplink/doc/10131/main/_html/relmapun008.htm seems to imply that you just create this new table and insert the keys.

Is this so?  I have been trying the following:

CREATE TYPE a AS OBJECT (
aKey  integer,
...
aBtable  relatesTo_list
);
/

CREATE TYPE b AS OBJECT (
bKey  integer,
...
bAtable  relatesTo_list
);
/

where 'relatesTo_list is a table of REF c' and c is:

CREATE TYPE c AS OBJECT (
aTable    REF a,
bTable    REF b
);
/

Obviously this is incorrect as I cannot get it anywhere close to working.  I can create tables and have it compile but I cannot insert any data (infinite loop errors).

So... I assume it is easiest to answer with an example rather than trying to correct my nonsense above, but HOW DO YOU DO THIS!?  Please!  It really can't be hard!

Thanks,
J
ASKER CERTIFIED SOLUTION
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 1 Comment.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 1 Comment.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004