Hello Bryan,
I think you may already have gotten an answer over on ticket, but let me put back the elements here if it can interest the rest of the community.
The bottom line is that the foreign key value should be the primary key of the parent entity. There are many ways to set a primary key, so it can be up to you to determine the best attribute to use. For information on how to do this, please refer to the documentation here: https://www.semarchy.com/doc/semarchy-xdm/xdm/latest/Design/logical-model/create-entities.html#_create_a_new_entity
If your need is that you have multiple tables, and each table wants to join on a particular column, i.e Sales table joins on CountryID, Customer table joins on CountryName, Product table joins on CountryAlpha2Code.
This is not achievable with the current design of the solution.
As only 1 value is stored in a foreign key: the primary key of the parent entity.
So, this needs to be addressed directly in the integration process.
If 1 system is sending the CountryName then they need to do a lookup on the Country entity to bring back its ID, and so on and so forth.
Hello Alexia,
Thanks for the in-depth response!
Regarding this "So, this needs to be addressed directly in the integration process.
If 1 system is sending the CountryName then they need to do a lookup on the Country entity to bring back its ID, and so on and so forth."
Hello Bryan,
You've asked to be put in contact with the Professional Services team to dig deeper on this topic, this is done, you should be contacted in the coming days.
Just to close up your pending questions here, integration is not handled by Semarchy xDM.
If you'd like to use our integration tool (Semarchy xDI), you can do so :)
But yes, the bottom line is that as the primary keys and foreign keys are mandatory on integration and that the initial information (whatever it might be) is coming from the source systems, you need to handle them during the integration stage.
If you were thinking about enrichers, I'm afraid you'll be to late in the certification process to have a working stable solution.
But that is just my point of view.
Professional Services may have more robust implementation ideas in mind.
Bryan LEE
I am enquiring if Semarchy XDM has a way to choose the attribute to be used as a Foreign Key for the Reference Relationship.
An illustration:
I have a Country table, with the following attributes
CountryID (ID, auto-increment start from 1)
CountryName
CountryAlpha2Code (unique codes, e.g., US, UK, SG).
I want a reference relationship with a Business Unit table. The Business Unit table has an entity column where there are CountryAlpha2Codes like US,UK,SG.
How do I create a Reference Relationship between these two tables? Alternatively, can a Reference Relationship in xDM be configured to use CountryAlpha2Code (non-ID) as the foreign key, or must it always store the Country entity’s ID attribute?
If so, how do i proceed to map/import CountryAlpha2Code in BU so that the BU → Country reference is set?