Map many to many relationship in object relational database (Oracle)

Question

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 How to design many-to-many relationships in an object database?

Book {
 Collection<Category> categories
}

Category {
 Collection<Books> books
}

To define a collection in a table in ORDBMS you have to use a nested table. (Example taken from oracle website Sample Application Using Object-Relational Features)

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 */

However it is best to keep a collection of references rather than keeping the objects itself.

According to the this answer Nested table primary and foreign key in Oracle it is not possible to add a foreign key to to a nested table.

1) So what is the best way to map a many to many relationship in object relational database (Oracle)?

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?

score 0 · Accepted Answer · answered Apr 16 '19 at 17:59

This is how i solved the issue. As mentioned in the question a collection of each object has to be kept in both the objects when it comes to many to many mapping.

      /*-------Creating types-----------*/

     /*Incomplete type of BookCategory nested table- To keep a collection of categories which the book belongs*/
     CREATE TYPE BookCategory_tbltyp;
     /*Incomplete type of CategoryBook nested table - To keep a collection of books which the category contains*/
     CREATE TYPE CategoryBook_tbltyp;

     /*Creating Book type*/
     CREATE TYPE Book_objtyp AS OBJECT(
        Id VARCHAR2(6),
        Name VARCHAR2(30),
        BookCategoryList BookCategory_tbltyp
     );
     /*Creating Category type*/
     CREATE TYPE Category_objtyp AS OBJECT(
        Id VARCHAR2(6),
        Type VARCHAR2(30),
        CategoryBookList CategoryBook_tbltyp
     );

    /*Creating BookCategory object type*/
    CREATE TYPE BookCategory_objtyp AS OBJECT(
      CategoryId VARCHAR2(6),
      Category_ref REF Category_obj
    );

    /*Creating CategoryBook object type*/
    CREATE TYPE CategoryBook_objtyp AS OBJECT(
      BookId VARCHAR2(6),
      Book_ref REF Book_obj
    );

    /*Completing incomplete BookCategory and CategoryBook nested table types*/
    CREATE OR REPLACE TYPE BookCategory_tbltyp AS TABLE OF BookCategory_objtyp;

    CREATE OR REPLACE TYPE CategoryBook_tbltyp AS TABLE OF CategoryBook_objtyp;

    /*-------Creating tables-----------*/

     /*Creating book table with the nested collection table*/
     CREATE TABLE Book_objtab OF Book_objtyp(
        PRIMARY KEY (Id)
     )
     OBJECT IDENTIFIER IS PRIMARY KEY
     NESTED TABLE BookCategoryList STORE AS BookCategoryList_tab(
          PRIMARY KEY(NESTED_TABLE_ID, CategoryId)
        )
        ORGANIZATION INDEX COMPRESS)
     RETURN AS VALUE;

    /*Creating category table with the nested collection table*/
     CREATE TABLE Category_objtab OF Category_objtyp(
        PRIMARY KEY (Id)
     )
     OBJECT IDENTIFIER IS PRIMARY KEY
     NESTED TABLE CategoryBookList STORE AS CategoryBookList_tab(
          PRIMARY KEY(NESTED_TABLE_ID, BookId)
        )
        ORGANIZATION INDEX COMPRESS)
     RETURN AS VALUE;

    /*Foreign keys are not supported in nested tables. So the solution is to use SCOPE. 
According to oracle documentation SCOPE cannot be used in CREATE TABLE statement. 
So we have to use ALTER TABLE*/
    ALTER TABLE BookCategoryList_tab
        ADD (SCOPE FOR (Category_ref) IS Category_objtab);

    ALTER TABLE CategoryBookList_tab
        ADD (SCOPE FOR (Book_ref) IS Book_objtab);

The SCOPE constraint is different from the referential constraint in that the SCOPE constraint has no dependency on the referenced object. Ex: row object in Category_objtab may be deleted, even if it is referenced in the Category_ref column of the nested table.

Refer Sample Application Using Object-Relational Features for more info.

Map many to many relationship in object relational database (Oracle)

1 Answers1