If you’re implementing Salesforce for the first time or making changes to your data model, you might’ve heard your Salesforce admin or consultant use the terms master-detail or lookup relationships, but what does that mean?
Simply put, master-detail and lookup relationships are two ways to connect your data in Salesforce. At a high level, master-details make two data tables or objects tightly related, while lookups are more loose.
In this article, we’ll cover the specific characteristics of each relationship, some of their limitations, and even use a hypothetical, insurance-based case study to show those relationships in action.
What is a Master-Detail Relationship?
Of the two relationship types, a master-detail is stronger and more tightly-coupled. When you use this relationship type, one object is the master or parent and the other is the detail or child, and just like when we were kids, the parent controls certain behaviors of the child (much to our chagrin).
For one, the detail inherits security settings from the master. This just means that if you’re able to view, edit, or delete a parent’s data you can do the same with the child’s data.
Second, the child can’t exist without a parent. If you were to delete a parent’s data it would cause what’s known as a cascade delete, meaning all of that parent’s children would be deleted as well.
Finally, master-details allow you to create rollup summary fields on the parent. This field can automatically summarize data from its child records using one of four functions:
- Count: the total number of child records related to the parent.
- Sum: The total value of a field on the child records, such as the total cost.
- Max: The highest value of a field across the child records, such as the most expensive cost.
- Min: The lowest value of a field across the child records, such as the least expensive cost.
Example
A simple example of this relationship is an invoice with line items. The invoice acts as the master or parent and the line items are the details or children.
If you received an invoice but couldn’t see the breakdown of the individual line items on it, you might get frustrated by the lack of transparency – “Why am I getting charged this amount?”
Likewise, if you had line items that weren’t tied to a specific invoice, your data could become very confusing – “What are these line items for and where are they supposed to go?”
It’s a perfect use case for a master-detail. If you were generating an invoice for a customer, you could add line items to it and the cost for each one would get rolled up into a total on the invoice. Your ability to change or delete data for an invoice would be true for its line items as well.
Similarly, a customer should be able to view an invoice but not make changes to it. As much as we wish we could change the cost of each product at the checkout line, it would be a recipe for disaster if preventing a customer from editing an invoice didn’t also apply to the line items.
If you needed to delete the invoice, you wouldn’t want the customer to be left with a bunch of orphaned line items nor would you want to have to go through and delete each one individually before you could delete the invoice. This is where the cascade delete feature of master-details comes in handy.
Limitations
While master-details are powerful for creating those tightly-coupled relationships, there are a couple limitations.
First, standard objects can’t be the details in a master-detail relationship. Things like Accounts, Contacts, and Opportunities can only be used in this relationship if they act as the master or parent.
Second, Salesforce limits you to two master-details per object, meaning a child can only have two parents through this relationship type. If your data model requires a child to have three or more parents, then it’s time to evaluate which of those relationships should be lookups.
What is a Lookup Relationship?
Compared to master-details, a lookup is a more loosely coupled relationship between objects. This means that the child’s behaviors aren’t as reliant on the parent object. Just like growing up, you may “look up” to your parents, but you largely exist and function independently from them.
Because of this independence between objects, a child isn’t required to have a parent by default. They can look up to a parent record, but they don’t have to.
This prevents cascade deletion. By default, if you delete a parent in a lookup relationship its children won’t automatically be erased. The field referencing the parent would just become blank.
The looseness of this relationship also means that the security settings are set independently for each object – the ability to view, edit, or delete a parent is not automatically inherited by the child.
Unlike a master-detail, a lookup relationship also allows you to make custom objects a parent of standard objects like Accounts and Opportunities.
Example
Let’s say we want each line item on an invoice to reference a specific product. By creating a lookup relationship between the two, a line item could “look up” the product it’s related to while still keeping them independent of one another.
Your product development team would be able to create and make changes to a product without having to be able to edit or even view invoices and their line items.
If you discover that there are two duplicate products, deleting one of them won’t erase all the line items with it, saving your finance team the headache of having to reconcile invoice lines that have mysteriously vanished. However, to maintain clean data, you’ll likely want to re-parent the lines to the product that you’re keeping before deleting the duplicate.
Limitations
Lookups come with a couple limitations as well.
For one, you don’t have the ability to create rollup summaries on the parent like you do in a master-detail relationship. However, you can still show that summary data through reports and dashboards. There are also products available on the AppExchange for you to implement rollups on lookups.
Second, Salesforce limits you to 40 lookups on custom objects, meaning your custom objects can have up to 40 lookup parents. Standard objects are limited to 25 lookups.
Which should I use?
We’ve covered a lot of information on the differences between master-details and lookups. If you’re still uncertain of which one to use, a quick way to determine that is to ask yourself some of these questions:
- Should the child always have a parent/should the child not be able to exist without a parent?
- Do we need a measurement field on the parent such as a count, sum, min, or max of the child records?
- Do the parent and child objects need to always have the same security settings (ability to view, edit, or delete)?
- Do we need child records to automatically get deleted when the parent record is deleted?
If you answered “yes” to these questions, then you’ll likely want to create a Master-Detail relationship. Otherwise, a Lookup relationship would be more appropriate.
Of course, there are exceptions to this, which we’ll cover in our case study.
Case Study
Premise
For our case study, we’ll follow a mid-market insurance company called Bowline Insurance. Marina, the Salesforce admin at Bowline, wants to implement a data model that allows the agency to track policies they’ve sold as well as the insurance carriers and products that the policies are associated with.
To start, she’ll need to create three custom objects – Carrier, Carrier Product, and Policy. She’ll then determine whether she needs to create master-detail or lookup fields based on how those objects should relate to one another.
Master-Detail
Let’s start with Carrier and Carrier Product. Marina doesn’t want carrier products to exist without a carrier. It also makes sense for the Carrier object to control the behavior of its Carrier Products. If a user is able to edit a carrier, we likely want them to be able to edit the carrier products as well. Similarly, if someone shouldn’t have edit access to carriers, we wouldn’t want them to be able to edit carrier products either.
For these reasons, the best option would be a master-detail relationship where Carrier is the master and Carrier Products are the details. This also opens up the possibility of using rollup summaries.
Say for example, there are different commission rates that Bowline can earn for each carrier product. A rollup summary on the carrier could show us at a glance what the minimum or maximum commission rates are for policies sold through that carrier.
Lookup
Relating Policies to Carriers and Carrier Products may be a little less clear-cut. For one, Marina always wants policies to be associated with a carrier and carrier product. It would also be nice to have rollup summaries showing the total number of policies sold for each carrier or the sum of policy premiums, and by extension, commissions Bowline is earning for a particular carrier or product. It seems like a perfect use-case for a master-detail relationship, but our main sticking point is the inheritance of security settings.
Marina wants insurance agents to have read-only access to Carriers and Carrier Products. She doesn’t want them to be able to change commission rates nor add or remove products offered by the carrier. However, agents need to be able to edit a policy as they’re closing a deal. Since Policies need to have security settings independent of their parents (Carrier and Carrier Product), a lookup relationship is more appropriate.
To ensure we don’t have any orphaned policies, Marina is going to make these lookup fields required. She can also create reports and dashboards to show the total number of policies, sum of policy premiums and commissions per carrier and product. If it’s important for users to see that directly on a carrier record, she’s also found some free tools on the AppExchange, such as Rollup Helper, that can accomplish that for objects with lookup relationships.
Ultimately, Bowline’s data model will look similar to this:
Happy Little Accidents is an insurance carrier that offers umbrella, home, life, and auto products. If that carrier is deleted, its products will be automatically deleted as well. While policies won’t be automatically deleted, they’ll have to select a new carrier and carrier product before Happy Little Accidents can be removed from the org.
The agent that sold policies to Liz Lemon and Adam Apple can make alterations to those policies, including changing the carrier or product associated with them, but they can’t make any changes to Happy Little Accidents or its product offerings.
In Summary
Master-details and lookups have a lot of unique characteristics to consider when determining which one is best for relating objects to one another.
To recap: a master-detail is a stronger relationship where the child object is dependent on the parent object, whereas a lookup is a more loose relationship where the child object can exist without a parent.
A more detailed summary of the differences is listed below.
Master-Detail | Lookup |
|
not required and can be null or blank) |
|
|
|
|
|
|
|
|
|
|
If you still can’t decide which relationship type is going to be best for your data model long-term, don’t sweat it! In most instances, you can switch a master-detail to a lookup field and vice versa. However, if your org has a complicated data model with many layers and related objects, it may be best to do some additional research on the implications or reach out to a consultant before doing so.
If you’d like to learn more about object relationships, master-details, and lookups, we’d highly recommend completing the Data Modeling badge in Trailhead or checking out some of the other articles linked in additional resources.
