We are integrating a new system into an existing vault. The existing DV is sourced from the “Enterprise” system and the new system “Access” holds information about security access such as card swipes at doors (the current topic.) The PK for all Hub tables in current DV use a HashKey which contains a hash of all relevant BK columns. The current HubPerson table does not employ a BusinessKeyCollisionCode (BKCC). What is the proper way to handle this?
In the diagram, the green table represents the current state.
The blue tables represent possible solutions in increasing order of effort.
Option 1 simply adds a BKCC as a prefix to the “Access” BK, inserts that into the PersonID_BK column, and the hashed value into the Person_HK column.
I think this is likely not ideal.
Option 2 Add a BKCC column, here called CollisionKey, and load the “Access” BK into PersonID_BK, the BKCC into CollisionKey, and then hash the concatenated values of CollisionKey and PersonID_BK into Person_HK. The values for the “Enterprise” BKCC are not filled in, and the “Enterprise” Person_HK is not re-calculated.
This is my preferred solution, because I do not want to undertake the effort to recalculate the Person_HK for the “enterprise” system everywhere it enters into the existsing DV. This imples that only new sources will leverage the BKCC going forward.
Option 3: Add a BKCC value for every source, including the existing “enteprise” system and eat the cost and effort of re-engineering the DV.
I hope it doesn’t come to this.
