We are soon to implement a solution where people (Person__c) can delegate jobs to other people, and which jobs depend upon a person's Role. (Role__c). The role of a person depends on the delegation relationship, e.g. Person A has Role 1 when acting as delegate for Person X, but has Role 2 when acting as delegate for Person Y. Each Person may have multiple delegates.

I would generally achieve this using a many-to-many relationship between Person (Person__c) and delegate (Also a Person__c). The join table would be Role_Mapping__c with the following fields:

  • Manager__c Master-Detail to Person__c
  • Delegate__c Master-Detail to Person__c
  • Role__c Lookup to Role__c

The Architect agrees that it is the correct way to do it if we were using a relational database, but in Salesforce you shouldn't do it that way, instead, he suggested adding extra fields to Person__c:

  • Delegate_1__c lookup to Person__c
  • Delegate_1_Role__c lookup to Role__c
  • Delegate_2__c lookup to Person__c
  • Delegate_2_Role__c lookup to Role__c
  • Delegate_3__c lookup to Person__c
  • Delegate_3_Role__c lookup to Role__c

Which seems dreadful to me. I challenged him and he has not been able to convince me that he's right. Is there any justification for suggesting such a ghastly approach?


Perhaps too opinion based here, but, avoid the latter.

If you're having Delegate_1__c, Delegate_2__c etc... Looking at the same object and Delegate_1_Role__c, Delegate_2_Role__c etc... Looking at the same object, I see no reason why you couldn't just have two fields: Delegate__c and Role__c in their own object, assigning each delegate to a role, storing them in there own records.

I should say from experience I've come across objects that used a Person_1__c, Person_2__c, Person_3__c approach and it becomes nasty to use in the future even if it isn't evident straight away.

Anyway, a more factual answer:

Salesforce's solution to a many to many relationship is simply junction objects which work exactly as you describe in your solution.

You can use master-detail relationships to model many-to-many relationships between any two objects. A many-to-many relationship allows each record of one object to be linked to multiple records from another object and vice versa.

Doing it this way will make records easier to search and reduce metadata. What happens down the line if you get to Delegate_400__c and Delegate_400_Role__c, try to create a new one and realise you've used all fields for you object.

That's probably a bit outlandish, but theoretically it could happen.


If there is a finite number of roles as in the three above, it would definitely make sense to use the fields to solve your problem. In salesforce each record usually comes with a 2kb cost regardless of the number of fields. There are additional benefits in SOQL and reporting being simplified.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.