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?