ART#208 - How to do many to many mapping in ATG repository?



Once you have understood one-to-one and one-to-many mapping, many-to-many is a piece of cake. We always create a separate mapping table for many-to-many relations. It is very much similar to one-to-many mapping, with a slight difference.
In one-to-many mapping, If one "A" has many "B"s, then we create a table for "B" with a specific column to store the repository ID of "A".
Then, we use "B" as an secondary("multi") table in item-descriptor corresponding to "A", so that multiple "B"s are mapped to single "A"

In many-to-many mapping, we simply create "A" and we simply create "B", none of these two tables are aware of each other. Now, to define a relationship, we create a separate table "C" which contains the mapping of both of these entities.

EXAMPLE:

Let us consider an example, consider OOTB "user" item-descriptor. Now, there might be a requirement that a user can have multiple "favoriteProducts". Now, a particular product can also be associated with multiple users. i.e. a product can be favorite for many users. In this case, the mapping is many-to-many.
In this case, both "user" (from ProfileAdapterRepository) and "product" (from ProductCatalog) are OOTB (you can also use your custom item-descriptors in the same way).

DATABASE DESIGN:
Now, all we have to do is create a mapping table to store the mapping of user and product. See below diagram for more clarity.


Above, we can see the OOTB tables dps_user and dcs_product mapped via a custom table user_fav_prdt which store the mapping of user_id and product_id, along with the column sequence_num to store the sequence of addition. Please note that this column is required only in cases where you need to store the order of collections(e.g. Lists), it is not required for unordered collections (e.g. Set).

Let us move on the the repository XML design.

REPOSITORY XML:
The XML is also very much similar to one-to-many mapping. See below screenshot.



Since this is a many-to-many mapping relationship, we can also re-use table user_fav_prdt in product item-descriptor in the same way as above to refer to all users which have selected this product as favorite. We can have a property, which can be named something like "productUsers", which can have a list of all the users who have selected a particular product in their favorites list.

Now that we have understood various mappings, let us move ahead and understand the process to create a non-versioned ATG repository from scratch.


Back



Next






6 comments:

  1. Should you consider mentioning about differences with one-many (id-column-nameS etc.)

    ReplyDelete
  2. The basic difference between different mappings is mentioned at the link:-
    http://learnoracleatg.blogspot.in/2014/12/art205-what-mapping-should-you-choose.html

    Also, you can have a look at the code for one-to-many mapping HERE:
    http://learnoracleatg.blogspot.in/2014/12/art207-how-to-do-one-to-many-mapping-in.html

    These links should answer your question.

    ReplyDelete
  3. Hi Monis, one to many & many to many mapping looks similar.Can you mention out the key difference between them & how to identify whether the mapping belongs to which category among them?

    ReplyDelete
    Replies
    1. Please read ART#205-it has the basic details of all mappings.

      Delete
  4. Hello Monis!
    At the first paragraph of this article, you describe that "if A has many Bs, then a separate column should be created in table B with an ID for A", thus having only TWO tables for a one-to-many mapping.
    However, in the previous article, you describe that for a one-to-many mapping, a separate table should be created to map As with Bs.
    So, what gives? Am I missing something?

    ReplyDelete
    Replies
    1. The statement is for one-to-many mapping.
      For many to many mapping; a separate table should be created.

      Delete

Subscribe

Get All The Latest Updates Delivered Straight Into Your Inbox For Free!

Flickr