ART#206 - How to do one to one mapping for ATG repository ?

Now that figuring out which mapping should be chosen for our repository design, let us understand how actually "one-to-one" mapping is done. We will first understand, how it is done out-of-the-box and then we will move on to creating database and repository XML fromscratch.Out of the box ExampleIn the ProfileAdapterRepository, there is an item-descriptor "contactInfo" which comes out of the box. This item-descriptor is basically used for storing address details (addressLine1, addressLine2, addressLine3, city, zip etc.). Now this item-descriptor is used in a lot of places like "homeAddress", "billingAddress" etc in the user item-descriptor.For out-of-the box mapping, you can have a look at the definition of "homeAddress" property of "user" item-descriptor via dyn/admin (or you can have a look below).This would be a perfect out of the box example of one-to-one mapping as a user can have exactly one "homeAddress" and an address can have one and only one "user".You can have a look at below reference XML directly from the corresponding item-descriptors. Please note that this is ATG's out of the box XML definition, and we are just understanding how this is set-up. There is no intention of copying any of ATG's XML.

Below is the simplified version of OOTB "contactInfo" item-descriptor definition. We have stripped some attributes from the tags, as it might lead to unnecessary confusion. We will also understand various repository XML tag attributes in later articles.

It contains various details for address like address lines, city, state, postal codes etc. These details are stored in dps_contact_info table, which is present out of the box.Now, let us have a look at out-of-the-box "user" item-descriptor, where the mapping of "homeAddress" property is done. Below is snippet of "homeAddress" property from "user" item-descriptor (some attributes have been removed for simplification).

Here, we have an auxiliary table, dps_user_address, which stores the repository-id of a "contactInfo" item.ATG has used a separate auxiliary table, because this table also has some other properties. But, under ideal situations, this property could have been in primary table(dps_user) of "user" item-descriptor itself, with home_addr_id column present the table dps_user.Example from scratchNow, let us move on and understand, how can we create a one-to-one mapping right from scratch.We will take up an example like we took up in ART#204 - Adding a new item-descriptor. Let us consider, a user has a card to store points on every purchase. So once the points in the (virtual) card reaches above a certain limit, the user can redeem those points for purchase and get a discount. Let us say, that a user can have only one "pointsCard" and a "pointsCard" can only be associated with a single user. This way, it is always a one-to-one relationship.Firstly, we have to create an item-descriptor "pointsCard" exactly as we did in ART#204.Next, we have to associate it with our "user" item-descriptor. This goes in two-steps:-1. Database ChangesFirstly, we have to create a new column to accommodate the repository-id of the new "pointsCard" item, so that they are linked. Since we NEVER EVER change out of the box tables, we have to create an auxiliary table for storing repository-id of "pointsCard" item.Let us see how. Now, any auxiliary table of any item-descriptor MUST have a column to store the repository-id of the item-descriptor as described in ART-203. Now let us have a look at our auxiliary table below.

1. We create an auxiliary table "CUSTOM_USER" with "USER_ID" which corresponds to the "ID" column of OOTB "DPS_USER". This column is foreign key for DPS_USER's ID column. Hence, they store same value.2. Next, we add a column "POINTS_CARD_ID" to our custom auxiliary table to store the repository-id of "pointsCards" item. This would associate the "pointsCard" item-descriptor with "user" item-descriptor in a one-to-one manner.3. Next, we create a primary table for pointsCards item descriptor just like we did in ART#204 - Adding a new item-descriptor. (You can refer to ART#204, in case you missed it out and want more details on creating item-descriptor. Same example is used there)2. Repository XML changes1. This is pretty much straight-forward. We create an item-descriptor XML for "pointsCard" item-descriptor exactly as we did in ART#204).

2. Next, we have to associate the pointsCard item-descriptor with user item-descriptor.Let us see that below. (We are assuming, you have already created pointsCard item-descriptor in userProfile.xml as described in ART#204)

3. In above screenshot, we have specified, the auxiliary table name, along with the column to store the repository-id of user item-descriptor. This is specified by the attribute id-column-name of the table tag. Next, we specify a property name, by which we want to refer the pointsCard item-descriptor (we name it as pointsCardItem). We specify the column name which stores the repository-id of pointsCard item-descripto. Finally, we tell the user item-descriptor that this property is of type pointsCard (which is another item-descriptor in the same repository)NOTE: We can also refer to an item-descriptor in some other repository. In that case, you would have to specify a repository attribute in <property> tag. It shows something like:-<propertyname="pointsCardItem" column-name="points_card" item-type="pointsCard" repository="[the nucleus path of some other repository]" />Now, we are clean with one-to-one mapping, we can move on and understand one-to-many mapping in ATG repositories.

5 comments:

pretty good article, but i found a mistake in xml file definition, you write: column-name="points_card", but should be: column-name="points_card_id", it's little mistake but some one can don't understand why their repository doesn't work correctly)

I've few queries. Please do help me understand as I'm very much new to this learning.

1. In above example, you created an item-descriptor for point-card in the same Xml, So doubt is can we create an separate Xml for point card and then it can refer to each other.?

2. when should we create an separate xml?

3. What does that repository means exactly?. Is it the xml file we create with item-descriptor or the item-descriptor itself is a repository.

4. In ART#204 you refer an userid in the point card table itself but here you have use an third table to map both the tables. So which is the correct approach of doing that. Do we really need an third table here?

About Me

Monis is a Software Engineer, Technology Enthusiast and an Entrepreneur. Monis has 5+ years of experience in Java based technologies including Oracle ATG, Broadleaf Commerce, Hybris, Apache Solr, Hibernate, Spring with specialization in eCommerce platforms.