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 Example
In 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 scratch
Now, 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 Changes
Firstly, 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 changes
1. 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)
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:-
<property name="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.