I have a child object that I need to create which would be related to Opportunity. Let's call it RepPay. I wanted to know what is the best way to create this relationship for querying purposes which I will need to perform later. I want to filter out Opportunities through SOQL bases on a boolean value on the RepPay object. The goal of this is to keep track when one of our reps has been paid. Currently we have a boolean on the opportunity object however we want to move this boolean into another related object so there is no issue with resource locking happening on the opportunity object when we try to update the boolean through .NET code.
I have three methods that I considered doing, but I am not sure which one follows best practices:
- Create a Lookup field on the Opportunity object. This lookup would get set when the opportunity is first created and the boolean value on the RepPay object would be defaulted to false. Then in the SOQL query reference this look up object by saying [Select Id, RepPay__r.Id, RepPay__r.Paid From Opportunity where Id = 'asdfadf' and RepPay__r.Paid = false] to obtain opportunities where our rep hasn't been paid. (Side note: I noticed with this solution only the ID of the RepPay object is displayed on the Opportunity page, I'm not sure if there is a way to display something other than the ID of the lookup object, maybe the boolean field on the RepPay object? One solution I found was creating a lookup from the RepPay object to the Opportunity object which would display in the Related Lists area of the Opportunity, however creating the lookup on my child object doesn't allow me to use dot notation when querying from the Opportunity object, so I think that I would need to create a lookup in both directions to accomplish my goal with this method)
2.Use a subquery to pull all opportunties which don't have RepPay object related to them, that way we can update those opportunities that have not had their reps paid. (There is additional criteria which qualifies the opportunity) Ex: SELECT Id from Opportunity WHERE Id NOT IN (SELECT Opportunity_Id__c FROM POC_Ob ject__c)
3.Use a master-detail relationship on the RepPaid object and then use a rollup field on the opportunity object to keep track if there is a reference to RepPaid. If there is not at least one reference then we will assume this rep has not been paid yet. Then my code will create the relationship once an opportunity meets the criteria for paying the rep. Therefore creating an entry in the RepPaid object and referencing it to the opportunity.
My question is would Method 2 or method 3 have a better performance time. I'm not sure when the rollup field is usually calculated and if there are hundreds or thousands of opportunities if this would cause a performance issue. Method 1 is a little extra work since there needs to be two lookup references created and set for everything to be displayed properly.
Please let me know if any of this doesn't make sense and I can clarify/provide some better examples.