title: Map many to many relationship in object relational database (Oracle)
body: <p>If you have a many to many relationship between book and category you can map it to  object oriented model as shown below based on <a href="https://stackoverflow.com/questions/1069187/how-to-design-many-to-many-relationships-in-an-object-database">How to design many-to-many relationships in an object database?
  </a></p>
  
  <pre><code>Book {
   Collection&lt;Category&gt; categories
  }
  
  Category {
   Collection&lt;Books&gt; books
  }
  </code></pre>
  
  <p>To define a collection in a table in ORDBMS you have to use a nested table. (Example taken from oracle website <a href="https://docs.oracle.com/cd/B19306_01/appdev.102/b14260/adobjxmp.htm" rel="nofollow noreferrer">Sample Application Using Object-Relational Features</a>)</p>
  
  <pre><code>CREATE TABLE PurchaseOrder_objtab OF PurchaseOrder_objtyp (  /* Line 1 */
     PRIMARY KEY (PONo),                                       /* Line 2 */
     FOREIGN KEY (Cust_ref) REFERENCES Customer_objtab)        /* Line 3 */
     OBJECT IDENTIFIER IS PRIMARY KEY                          /* Line 4 */
     NESTED TABLE LineItemList_ntab STORE AS PoLine_ntab (     /* Line 5 */
       (PRIMARY KEY(NESTED_TABLE_ID, LineItemNo))              /* Line 6 */
       ORGANIZATION INDEX COMPRESS)                            /* Line 7 */
     RETURN AS LOCATOR                                         /* Line 8 */
  </code></pre>
  
  <p>However it is best to keep a collection of references rather than keeping the objects itself. </p>
  
  <p>According to the this answer <a href="https://stackoverflow.com/questions/9018834/nested-table-primary-and-foreign-key-in-oracle">Nested table primary and foreign key in Oracle
  </a> it is not possible to add a foreign key to to a nested table. </p>
  
  <p>1) So what is the best way to map a many to many relationship in object relational database (Oracle)?</p>
  
  <p>2) If the answer is keeping two collections in the two objects as shown above, how to store it as a reference collection without directly storing it as the object? </p>