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.
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).
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.
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.