Master Key — Design considerations for Enterprise Entity Identifier
Master Data Management (MDM) is about having a single, consolidated view of the entities like Customer, Vendor, Employee, Product etc. The concept of Enterprise Entity ID (EEID)is an integral part of sound MDM implementation.
EEID is created and assigned when an entity starts its life cycle in MDM system and meets following criteria: assigned to all entities that are tracked by MDM and same EEID should not be assigned to more than one entity.
In this article I will enumerate and analyze different design considerations that needs to be evaluated and provide recommendation for each of the consideration related to EEID.
Design considerations
Can primary key of the entity generated by MDM be used for EEID
When an entity like customer or vendor is created in MDM, a unique key used as primary key is generated as part of the internal workings of MDM and to maintain data integrity with in MDM data model. Primary key that is generated would meet the requirements of the EEID, but it would be tightly coupled with MDM internal workings and also key can change for at least one entity during the consolidation process of deduplicating entities. Using MDM primary key as EEID breaks the system agnostic enterprise identifier role of EEID by exposing MDM internal workings to enterprise. Its best not to use primary key generated in MDM as EEID.
Where should EEID be generated — within MDM or outside MDM
Generating EEID with in MDM instead of using MDM primary key as EEID would seems like a natural choice if starting with clean slate. In cases of migrating from legacy data management system to MDM or migrating from one MDM product to a different MDM product there would be a need - that for a period of time there will be two systems filling the role of EEID generator and also we should consider that the EEID is already generated for existing entities. In such cases and to align with decoupling of EEID with MDM, Its best to generate EEID outside MDM to maintain independence from MDM implementation and support build to evolve.
Where should EEID be placed in MDM data model
As it was established that EEID should be generated outside MDM and to not use the primary key of MDM as EEID the next question would be where to store EEID in the MDM data model. It best to be stored in construct similar to other identifiers of the entity like SSN, Drivers License, Passport Number and maintain status like Active, Inactive with Start and End dates. EEID is an identifier similar to other business identifiers and is best to be treated as such with additional guarantee that EEID is populated for all entities unlike other business identifiers.
Can more than one EEID be assigned to an entity in MDM when duplicate records are identified and consolidated
When MDM identifies that it has two instances of same entity, a workflow is triggered to consolidate two instances into one instance. Consolidation can happen either manually by a data steward or automatically using data survival rules. But what happens to EEID and if the EEID is already shared with other systems with in the enterprise or even with partner enterprises. When an EEID is already shared with other systems then deleting one or both of EEID would become a problem as the cross reference linkage will be broken. In these cases all the EEID’s must be preserved for an entity but that would violate that EEID as a mechanism to uniquely identify the entity. To resolve the uniqueness issue its best one EEID for an entity would remain with active status and remaining EEID’s linked to entity changed to passive state with end date of the collapsed date.
Can a business key like SSN be used as EEID
EEID for some entities like Individual can be an Id assigned by external agency like Social Security Number or Passport number. The problem with using numbers assigned by external agencies are that even though they are present and unique for most of the entities there would be some special cases like infants or foreign domiciled parties that would not have the Id assigned by the agency and the entity should still be part of MDM. Its best that EEID be a key generated with in the enterprise than an id assigned by an external agency.
