Link to home
Start Free TrialLog in
Avatar of Joey_hunny123
Joey_hunny123

asked on

Oracle many-to-many

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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial