Multiple Contact 'types' on a Custom Object: Model with junction object or specific lookup relationships?

Question

Probably a general schema design question:

I have a Custom Object that will have several related Contacts. The contacts are grouped into types, for example "Agents", "Applicants", "Referees", etc; things that identify how the Contact is related to the Custom Object.

There seem to be two ways to represent these relations.

1) With a related list for each 'type' of Contact relation.

2) A new custom object with a 'type' field (picklist) and a lookup to a Contact.

Which of these is the better way to represent the relation between these objects?

Are there any pitfalls in either of the cases?

Answer 1

@greenstork is correct, there really is no true correct answer, its just a two options and each has pros and cons.

The main difference in options is that Option 1 would be a many-to-many and Option 2 would represent a 1-to-many relationship.

Here are some of the pros and cons as I see them. Note here, that some people may see some of the cons and pros and vice-versa, basically, a lot of this is subjective and really depends on the use case.

Option 1 - (many-to-many) is not standard functionality when it comes to relationships. In order to accomplish this you would need to create a junction object that had lookups or Master Detail relationships to both the contact object and your custom object. this is a much more flexible solution in that a contact could be related to more than 1 custom object.

Pros

• Contacts could have many relations to different custom objects.
• Different Related List for each relationship type

Cons

• More overhead as a custom junction object needs to be created and maintained
• Reporting becomes a bit more complex as a third object is now involved (junction object)

Option 2 - (1-to-many) is standard salesforce functionality when it comes to relationships. You simply just need to setup a lookup field on the contact object that looks up to your custom object. You could then put a type picklist field on the contact to indicate the type of relationship.

Pros

• Standard functionality
• Reporting is simple as it just involves the two related objects

Cons

• A bit rigid in that contacts can be related to one and only one custom object
• A single Related list for all relations, with a type field that differentiates the relationship type

Answer 2

There is no correct answer here. I think it hinges on how important these different types of relationships are to your business. For example, If you have an entire arm of your organization dedicated to Referees and they engage with your business is many significant ways, then it would make sense to define the junction object between your custom object and contact as it's own Referee object.

However, it's a much simpler data model to have one junction object with a type field (picklist) to distinguish between the different types of related contacts. I would advise going that route of one junction object unless you feel that the business owners and sys admins have such a clearly defined business need to keep these objects separate for the purpose of straightforward reporting and separation of related lists. More related lists however, is going to lead to a longer and "noisier" page layout, again pointing to seeing if you can get away with one junction object.