Sample Header Ad - 728x90

How to handle multiple changing ID's for a single person

0 votes
0 answers
274 views
I have a company with a single party table containing Member ID(the primary key), Subscriber ID, Member Sequence ID, Legacy ID, and a myriad of other identifiers from random source systems. Because they all live in a single table, it's not possible to capture the full scope of a person as they could have multiple unique ID's in this table (they can be assigned a new member ID for various reasons). They can get also get changes to their subscriber ID (or any other ID) as well, with no other changes. I've tried to figure out the best way to track a person, but am a bit lost. I've created a new table, DIM_PERSON that contains some attributes. I've linked this to DIM_MEMBER via FK so a record in DIM_MEMBER can be ID Person_dim_id Member_dim_id 1 123 100 2 123 200 Which will allow you to have a one-many membership ID's to a single person. However they can change subscriber ID's without changing member_id or person_ID so i'm wondering if I need to follow the same logic and build a new DIM_SUBSCRIBER table with foreign key for the two previously mentioned tables. Would this also be the same for all other ID's and each would need a separate dimension table? The idea is to pull a single person ID and ideally see a history of all data tied to various ID's within the fact tables.
Asked by Seve (1 rep)
Oct 7, 2019, 04:52 AM