TLDR; Is there a way to build a joint object between the campaign member and account objects? If not, what are some other methods to design the SFDC database to capture the specific individuals that referred someone.
We have a few referral programs which are captured as campaigns. We use both Person Accounts and Standard Account/Contact. We have referral programs where a Standard Account can refer a Person Account or a Person Account can refer another Person Account. We want to know the Standard Account or Person Account that referred someone.
I would think the best way to do this is to have a campaign which references the referrer (the Standard Account or Person Account) - however that seems like an administrative nightmare and isn't scale-able (ie. if we have 5000 individuals that can refer someone, that would be 5000 separate campaigns).
The next solution we were thinking was create a generic 'Referral' campaign and have a joint object between the campaign member the Account table to specify the referring Standard/Person Account. After looking into this, we found SFDC doesn't support master-detail to Campaign Member.
We currently have a lookup field on the campaign member to reference the account that referred them, but we can only capture the first referral - which would be nice to have, although not a requirement.
I'm looking for best practice on how to build the database to record multiple referral programs or if there is anyway to create a joint object between the Campaign Member object and the Account object.