ART#207 - How to do one to many mapping in ATG Repository?

Back



Next

We have conquered one-to-one repository mapping and design now, so lets understand one-to-many mapping.

Out of the box Example

Let us take an example of a different repository today, ProductCatalog. This repository contains all the data about your catalog, products, categories, SKUs etc. Let us have a look at product item-descriptor's childSKUs property.

This is a one-to-many mapping because, each product can have multiple child skus, but a child sku can have only one parent product. The primary table for storing details of a product is dcs_product. ATG has created an auxiliary table, dcs_prd_chldsku, which stores the mapping of product to multiple SKUs. This auxiliary table has only 3 columns, viz. PRODUCT_ID, SEQUENCE_NUM and SKU_ID. This SKU_ID is also mapped to dcs_sku table, which stores the SKU details.

Let us have a look at the simplified version of repository XML for this property.

1. Firstly, It is very important to define the attribute type in case of one-to-many mappings. We set the value of this attribute as multi so that the repository knows that this table contains multiple values (set/list/array/map) for a particular item.

In our case, our item is "product". This table holds multiple childSKUs for this item.

2. Next, we define a column to store the sequence of the items added in the list.

For example, a product (PROD100001) has 2 SKUs (SKU100001, SKU100002). The value of SEQUENCE_NUM column would automatically increment by one, in case a new SKU is added to thie product. When a new product (PROD200001) is added, and has two SKUs, the count  for this product starts again. Therefore, a sequence of all the SKUs for each product is stored.

3. For each property, we specify whether it is a primitive data type (boolean, int, double etc.) OR a it can refer to a RepositoryItem. Using component-item-type  attribute, we specify, which RepositoryItem it refers to. RepositoryItem can be of same or different repository. In our case, since it property holds a list of SKU RepositoryItems, we also specify a data-type  attribute to tell the repository that this is a list.

In case, our property was NOT a list and held a single repository-item (in one-to-one mapping), we'd have to specify the attribute item-type="sku" instead of component-item-type="sku". Also, in this case, we could skip the attribute data-type.

4. data-type, in this case specifies what type of collection this property is. Possible values can be list/set/map/array etc. (When clubbed with component-item-type attribute).

Example from Scratch

All right, lets cut to the chase and understand the real thing. You need to customize an item-descriptor, and use a one-to-many mapping.

In the previous article, we took an example, where a user can have one and only one pointsCard and a pointsCard can be associated with only one user. (ONE TO ONE mapping). 

We will be taking the same example here, but with a twist. The twist is that, a user can have multiple pointsCard in this case. Now, you must be thinking that why would a store provide multiple cards to a single user? Some stores have multiple types of pointsCard, for example, a silver card, for which you gain 1 point for every 10$ purchase, a gold card for which you gain 2 points for every 10$ purchase etc. etc. In this case, a store may provide a silver card user with a new gold card if he is consistent, or has a lot of purchases or the manager of the store has hots for a partiular user (yeah.. that shit happens!)

1. Database Changes

The database changes for this one is very very similar to the previous article, except for two things.

• Exclusive Table for one-to-many mapping: In previous article, we created an auxiliary table CUSTOM_USER to accommodate some extra properties in our user item-descriptor. Now, this table might contain some extra attributes also (mapped one-to-one with user), therefore we cannot re-use this table for one-to-many mapping. Please note that for every one-to-many mapping for an item-descriptor, a new table has to be created. Let us have a look at below diagram for a bit more clarity.

Now, like the examples we saw till now, we have a primary table dps_user, an auxiliary table to store one-to-one mapped properties custom_user. A primary table for pointsCard item-descriptor.

We created a new mapping table to store the mapping of user to multiple points cards. Since we want the list of pointsCard item-descriptor to be a part of user, we use the mapping table user_points_card as an auxiliary table in user-item descriptor (we will see how).

Also, we are using a combination of user_id and card_id as a primary key. Both these columns in user_points_card are foreign keys to their corresponding tables (user_id for dps_user table and card_id column for points_card table).

The sequence number column is used to store the sequence in which pointsCard item is added to a particular user. Each user would have its own sequence for its set of pointsCard. This feature is handled by ATG, and we can configure it in our repository XML.

Let us see, how we map this stuff in repository.

2. Repository XML Changes

Doing this is pretty easy. 
1. First, you need to create your pointsCard item-descriptor, which you can easily create now after reading ART#204.
2. Next, you need to map this item-descriptor to the user in "one-to-many" fashion. See below XML for details. This is much similar to the out of the box example we took.

Now that we have learnt the basics of one-to-many mapping in ATG repositories, we can move on to many-to-many mapping in ATG repositories. If you are creating a repository from scratch, and want to do one-to-many mapping there, you'll just have to hang on and go through our article on creating a repository from scratch.

Back



Next