How to connect tables with more than one field in common - Surrogate Key
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
Updated 03-02-2023
