cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member

mceclip1.png Analytical Need 

We may encounter a situation in which a table has several fields that together identify each record. Each of them may appear several times but their combination is unique.

mceclip2.png Modeling Challenge

In our data model, we have 2 tables that have more than one field in common.

mceclip3.png Solution

Logic:
Concatenate the fields to one custom field in each table.
SQL :
tostring(Country) + '||' + tostring([Employee ID])
The two pipes ('||') are only there to make this key readable. They are not mandatory, it is recommended to insert a separator between the fields for better readability.
In the example the employee ID is only unique per country and not by its own.
SurrogateKey.PNG
 Image 1. New Surrogate Field
General note : This solution is a recommendation only. You can still connect on more than one field. For low volumes (several million records) the concatenation has no real effect on performance. It is just a simplified way to view the schema.

 Attachments

 - 350 KB - SurrogateKey.ecdata
Rate this article:
Version history
Last update:
‎03-02-2023 08:52 AM
Updated by:
Contributors