How to set a 1 to 1 relationship correctly?

[god_prophet]

I've found other thread with the same question, but answer was not clear enough for me. 

I need to link "nms:recipients" (or our extended abc:recipients table) and abc:agents.  Both have the column codinterno. It is a PK in both schemas.

So in my abc:recipients (extension schema), accorind to docs, I should add Database mapping | Adobe Campaign:

<element integrity="own" label="Extension" name="extension" revCardinality="single" revLink="recipient" target="cus:extension" type="link"/>

So, my "1 to 1 link" should be:

<element integrity="own" label="Agents" name="Agents" revCardinality="single" revLink="recipient" target="abc:agents" type="link"/>

Right?

1.- So how can I specify the relationship is done based on codinterno that is a PK on both schemas?
2.- You only modify the recipients schema in this case, or should I add something to the abc:agents schema?

[ccg1706]

Hi @god_prophet, 

Regarding your questions:

1-To specify the relationship, as it is based in the codinterno field, you will need to add a <key> within the element definition inside the schema. What does this key is define the relationship key fields between two tables. Use this, as an example to take into account.

<element integrity="own" label="Agents" name="agents" revCardinality="single" revLink="recipient" target="abc:agents" type="link">
    <key internal="codinterno" external="codinterno"/>
</element>
*internal= codinterno --> Related to the codinterno field in the abc:recipients schema
*external= codinterno --> Related to the codinterno field in the abc:agents schema

2- You will only need to modify the recipients schema if you want a uni-directional relationship. If it is the case that you want to access recipients records from agents, you should add another link. Take this solutions as a guidance:

<element label="Recipient" name="recipient" revCardinality="single" target="abc:recipients" type="link">
    <key internal="codinterno" external="codinterno"/>
</element>

Additionally, I leave you with some useful doc links:

Database mapping guide 

Link management guide 

Kind regards, 

Celia

[god_prophet]

It is not clear if point onw should go on acb_agents or recipients... because in point 2 you mentioned "You will only need to modify the recipients schema ..." so with this you can imagine point 1 goes inside acb_agents.... whoever... point 1 has: 

target="abc:agents"

And you shouldn't target the same schema from within....

I've getting: 

The attribute 'external' is invalid (line 3, document 'abc:agents')
------
SCM-120001 Unable to parse schema 'abc:agents'
------
The graphical component 'schemaview5' has generated an error.

[god_prophet · Accepted solution]

I ended up with: 

So in abc:agents you add an element inside the main element of the schema (you don't need to modify recipients schema. revLink, revLabel and revTarget create the link in recipients for you):

<element integrity="normal" label="Recipient" name="recipient" noDbIndex="true"
revCardinality="single" revLabel="Agents (Link)" revLink="agents"
revTarget="@codinterno" target="nms:recipient" type="link">
<join xpath-dst="@codinterno" xpath-src="@CODINTERNO"/>
</element>

If you don't want your schema agents to be visible from Recipients, just delete the rev attributes:


So in abc:agents you add an element inside the main element of the schema (you don't need to modify recipients schema):

<element integrity="normal" label="Recipient" name="recipient" noDbIndex="true"
revCardinality="single" target="nms:recipient" type="link">
<join xpath-dst="@codinterno" xpath-src="@CODINTERNO"/>
</element>