Lookup or Junction Object: A Salesforce Architect’s Decision Guide

In the daily business of Salesforce Architects and Business Analysts, some requirements seem crystal clear, and the decision to create a Lookup to another object is quickly taken. But, there are times when it might be worth exploring the process and business setting around the requirement a bit deeper before incurring technical refactoring and time-consuming migrations, as these can significantly impact user experience, system availability, and maintenance costs. 

In this article, I present a decision guide based on three simple questions to reduce the risk of either thinking too small with a Lookup, or too big with a Junction Object.

Relationship Types in Salesforce

When two entities need to relate to each other, in technical architecture we can make use of mainly two solutions: 

• Lookup: Represents a one-to-many relationship
• Junction Object: Represents a many-to-many relationship

For the sake of simplicity, we will not cover the hierarchical parent-child or master-detail relationships.

Lookup Relationship

The Lookup relationship is created by adding a custom field to the object that will be the “many” in the one-to-many relationship and point to the “one” entity, which will have the incoming link.

In the above screenshot, we have added a custom field called “Order” as a Lookup on the asset object. Note how we can also define the “strength” of the relationship by specifying what happens if the Lookup record is deleted. Can an asset exist without the connected order?

Coming back to the definition of the Lookup relationship: In this example, one asset is connected to exactly one order and one order is connected to at least one asset. An asset might be the “physical” representation of a product (for example a specific laptop, with a serial number). 

Let’s get into a practical example. The order is part of a purchasing process, during which a generic product “Business Laptop” is bought and a specific asset is then linked to that order. If we then look at the specific laptop, we can trace back to the purchase order. Obviously, in this case, an order can refer to many assets. A customer might have also ordered a mouse and a keyboard. 

Hint: The ERD (Entity Relationship-Diagram) examples use the Crow’s foot notation, which might be challenging for non-technical stakeholders. The Chen notation might be an easier choice or just annotating the relationship with simple explanations.

Junction Object

The many-to-many relationship is created by adding a Junction Object that connects “n” assets to “m” orders.

For the Junction Object, we have created a Custom Object called “Asset Order”, which contains two Lookups, one to the asset and one to the order. This is how every link between an asset and an order is created. This many-to-many relationship can be visually checked on the “Schema Builder”:

If an asset can have more than just one order connected (initial sale, return, second sale), we could go back to the example with the specific laptop a customer has ordered. Other orders related to that asset could, for example, result from return processes (the customer wants to send back the item) or the seller offers to buy back the item, refurbishes, and sells it again as a second-hand article. 

In that case, the multitude of orders can be traced on the specific asset, as we can see in the following screenshot showcasing a particular asset and different orders from its lifecycle.

An Existing Example in Salesforce: Campaign Members

A great example to understand a Junction Object, and the many-to-many relationship is by having a look at Campaign Members.

One Campaign can be connected to multiple Contacts:

One Contact can be targeted by different Campaigns:

Analysis of the Business Process and Scenario

Let’s put the above examples in the right context. A business analyst and an architect are working on the handover between the lead-to-order and order-to-cash process of an eCommerce client. Lead-to-order is the process covering the qualifying, nurturing, and converting of a lead into an order, while the order-to-cash is related to all activities that follow from a won order to delivery and payment.

On the “edge” between the two processes, the generic articles (products) a customer has selected, as part of their cart, are linked with specific items that are in stock (assets).

The business analyst and the architect interview their main stakeholder for the lead-to-order process, which is the online marketing department. In their requirement, every product is linked to one order (which is already fulfilled with the standard order product). Based on that, they can run specific analyses and gain insights on conversion rates and other relevant KPIs. The product and the asset are narrowly connected, so the asset is as well modeled to be in a one-to-many relationship to the order, by using a Lookup.

Onboarding the Next Stakeholders

A couple of months later, the next department is onboarded onto Salesforce. This department is headed by the COO and has a focus on operations. The main topics to be achieved with Salesforce are the return logistics process and driving additional revenue with refurbished business. Upon interviewing process managers, the business analyst and the architect found the existing entity relationship incompatible with new requirements – return orders and the reselling of existing assets. 

They need to create a many-to-many relationship between orders and assets, as well as introduce different types of orders. This not only leads to a situation where an already running process needs to be changed, but also to a carefully planned “in-situ-migration” of existing data into the new Junction Object (let alone changing existing reports and re-training of the end users).

The 3 Question Decision Guide

Not every business requirement can be foreseen and sometimes a pragmatic solution today may be better than a ‘perfect’ solution in three months. But increasing the chance to recognize the need for a different approach other than the obvious one, can be achieved with three simple questions.

What Does the Lifecycle of the Relevant Entities Look Like?

This question is not related to the Salesforce object per se, but to the entity as part of a set of business processes. It is not needed to map the core process of the client on various levels, but having a general understanding of the end-to-end process, especially the one being supported by Salesforce and those interfacing, is crucial. 

For this question, the business analyst asks various stakeholders about the different statuses the entity can go through, today and in the near future. Here, it is important to separate between “real” requirements and “remote wishes” that might be less relevant and lead to overengineered solutions. When asking the COO about the assets, the business analyst would have discovered that an Asset does not “end” after the order-to-cash process, but is needed in issue-to-resolution and buyback-to-refurbish.

What Business Requirements Would a Different Solution Support?

The target of this question is to reverse engineer a solution into possible requirements. What if instead of using a Lookup, we went for a Junction Object: What would then be possible? In which cases would an asset be linked to various orders? The theoretical answers to these questions can be challenged with the stakeholders and used to check for a match with real business requirements.

This question can also be applied to implementations unrelated to Lookups and Junction Objects, as it provides a good testing ground for a broad analysis.

What Is the Impact, If Our Solution Needs to be Adapted?

This approach embraces the fact that a solution might not always meet 100% of today’s or tomorrow’s requirements. It lets the architect create a contingency plan and communicate it to the client in an open manner. “If we have to switch from a Lookup to a Junction Object because requirements are newly discovered or changed, then this is the impact and this is the plan…” The answer to this question also shows the possible cost of a solution towards another and helps the team be prepared beforehand and make informed decisions.

Like in the case of the second question, this impact analysis can be adapted to be used across various implementations as it helps planning for contingencies.

Summary

The design of a solid architecture in Salesforce is always based on the analysis of business requirements and their respective processes. By asking three simple questions shown in this guide, a team can reduce the risk arising from unknown or unidentified requirements that might lead to changes, refactoring, and migrations.

1. Lifecycle: What does the lifecycle of the relevant entities look like?
2. Reverse-Engineering Requirements: What business requirements would a different solution support?
3. Impact of Alternatives: What is the impact if our solution needs to be adapted?