Recently I did a Salesforce audit for a client. They had a number of issues with their instance, and wanted a review from an independent expert.
The main person responsible was an Accidental Admin, and wasn’t given sufficient time to design a modular and scalable data model. He did the best he could with the time he was provided.
In one situation, a custom object was created to manage specific events. These events could have a lead presenter and up to 5 co-presenters.
Six fields were used to indicate the presenters and each field was a lookup to the Contact object. It looked like this:
There are several issues with this approach
- It’s not scalable. What would happen if there were more than 5 co-presenters?
- Not all fields were being used (Co-Presenter 2 only had a 20% usage, and the subsequent fields were significantly lower)
- It doesn’t allow for easy reporting (e.g. find all contacts that presented at any event)
So I recommended the following data model using a junction object and a field indicating the presenter’s role. This is a more modular and scalable approach to solving this problem.
The results look like:
The takeaway
When designing an object, it’s OK to have 2 or 3 lookup fields to a common object. But if you need more, or you already know there will be more, use junction objects. They are like a friendly person that introduces two strangers.