Data model best practice with junction objects

Question

An opportuntiy can be associated to many custom object 1 and custom object 1 can be related to many opportunities so I created a junction object which solved for this. However I have been asked to also have accounts as an option as this custom object may not be necessarily be related to an opportunity.

Should I change the junction object and make the opportunity a lookup from the junction object and an account and custom object1 as master? Then I guess I would put validation in that if an opportunity is entered the account has to match?? It doesn't seem elegant to me. What's the best approach?

Answer 1

Using look-ups to a single junction object doesn't seem to be the proper way to go considering that ideally you'd have created a junction object for opportunity to custom_object1__c to have done that portion properly to begin with. You also wouldn't get the many to many relationship that you're looking for, but instead more of a Master-detail. I see no benefit to using a junction object to first connect account with opportunity since a relationship already exists that's a many to one relationship. That would seem to have a detrimental, circular effect and should be avoided.

Based on there being two (2) many to many relationships, it would seem to me that you'd need two junction objects, one that sits between custom_object1__c and opportunity and another that sits between custom_object1__c and account. That would seem to provide the proper relationship between custom_object1__c and both account and opportunity.

What you you'd get would look like what's below:

                        JunctionObj1 ]---[ opportunity
                       /           
   custom_object1__c -{
                       \
                         JunctionObj2 ]---[ account