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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros