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
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.