This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Cookie Policy. Click Preferences to customize your cookie settings.
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.
Modeling Challenge
In our data model, we have 2 tables that have more than one field in common.
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.
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
