Clear all

Same Business Concept, Different Business Key Structures?  

Posts: 63
Topic starter
Trusted Member
Joined: 1 year ago

I just wanted to sound out the community on the recommended approach for the situation where there is a business concept that has multiple business keys representations.

To keep it simple say there are two systems (CRM and Sales). The CRM system has a Customer table that represents Customers with a single numeric Customer_ID attribute. The Sales system may use a different key structure, a two part composite consisting of a Customer_Code and Customer_Seq. The question is, how should this be modelled? I can only think of two options.

Option 1: Two separate Customer Hubs, one for each distinct business key structure.

-- ----------------
PK Hk_Customer_CRM
BK Customer_ID

-- ----------------
PK Hk_Customer_Sales
BK Customer_Code
BK Customer_Seq

Two distinct structures with non-nullable business key fields.

Option 2 : A single Customer Hub with flexibility for different business key structures

-- ----------------
PK Hk_Customer
NK Nk_Customer
BK Customer_ID
BK Customer_Code
BK Customer_Seq

The BK fields are populated according to the source business key, and the Natural Key (NK) is populated with the BK attributes that have been populated. The business keys may be nulled (but not all of them).

Both methods seem to work, but for me, the second option seems to be the way to go. Here we have a single Customer concept and, therefore, a single point of integration. However, I have heard that the first option is the recommended approach, I'm just not quite sure why?

13 Replies