Self referencing dimension - How to store Parent Key/Id as type II change

Hi all,

I am currently trying to model a situation, similar to an employee and manager model, where each 'Associate' has a 'sponsoring associate', and can themselves sponsor other associates. The depth of the hierarchy is, in theory, infinite, and ragged. Sometimes an associates sponsor can change, and this history needs to be tracked. The Business Key is Associate Id. So within DimAssociate, I currently have the Sponsoring Associate Name, and AssociateId (Business key) as Type II changes. When an Associates sponsor changes, a new type II record is created for that Associate Id, containing the new Sponsoring Associate Id and Name. The PrimaryKey of DImAssociate is the surrogate key, AssociateKey


What are everyone's thoughts on this method of tracking this type of history? I have wondered if I should be storing the current associate Key of the sponsoring associate instead of the Associate ID. My reasons for storing the Associate Id of the sponsoring associate is that I don't want to overly complicate things by storing the AssociateKey, and then having to be concerned about updating it to the current AssociateKey etc. However, I am not trying to use this dimension in a cube in SSAS and want to set it up as a parent child hierarchy, but in order to create the self reference, the Usage and Parent IDs must be the PK of the table. As currently designed, the PK is the AssociateKey, not the AssociateID that is used as the self referencing column.

I hope my explanation is clear.

Thanks!
Erik

how about creating a bridge to store the associations that do comprise of the natural key's instead of surrgoate key's.

dimassociate -> AssociateId, Associatekey, associatename, associateage, startdate, enddate, currflag
BridgeAssociation -> AssociationId, Associatekey, sub-associatekey, startdate, enddate, currflag