Reading a Salesforce Data Model for the Accidental Admin

Written By Brooke LaPointe

One of the most powerful tools in your Salesforce toolbox will be knowledge of the data model. Salesforce provides us with a comprehensive overview of how their data modelling works which you can see here. But you don’t have enough coffee or time to read this and understand it. It’s a whole new language.


You’re also faced with a mountain of reports and a need to quickly remember how all the data is connected. If you’re using NPSP, you may be faced with this data model and an overwhelming number of lines with strange symbols on the ends. Let’s translate these symbols into simple Salesforce terms.

 

The Broom

If you can understand these two symbols, the others fall in line:

At the bottom you see 3 lines, like a simple drawing of a broom. That’s what I’ll call that end from now on. It means many. On a Salesforce object, this is a related list.

At the top you see a horizontal line. Keeping with the broom, let's call this a handle. It means one. On a Salesforce object, this is a lookup field.

This is mostly straight forward. Let’s make it a little confusing: when you read these connectors you start on object a, read the end touching object b, then read object b.

Reading The Connectors Examples

Example 1:

Address(1) has many (related list)(2) Contacts(3).

In an NPSP org, you can see this relationship on the Address record.

Example 2:

Contacts(1) have one (lookup field)(2) Address(3).

In an NPSP org, you can see this relationship on the Contact record.

If you want to create your own developer NPSP org, you can do so on trailhead here.

 

Required Field

The connectors we just looked at rarely appear on their own (a single handle and a broom). There are additional symbols on each end.

Just like the broom handle meaning one, the second line also means one. You can read the handle with an extra line (the double line connector) as one, and only one. In Salesforce terms, this is a required field. 

So what does it mean when the line is above the broom? One or many - never none. A required related list. I have not seen it on a Salesforce data model yet. If you have a data model that includes a database outside of Salesforce, you may see this symbol. 

If the second line means it’s required, you can logically conclude that the empty circle means none or not-required. And you’d be correct. A handle (line) with an empty circle is a not-required lookup field. A broom with a circle is a not-required related list - it may be empty. 

In the Address and Contact example, you see the circle on both ends. A Contact does not need to have an Address - the lookup field is not required. And an Address can exist without a Contact - the related list may be empty.

 

Master Detail

The final connector that matters when reading a Salesforce data model is the little empty diamond.

This means there is a master-detail relationship. The diamond is on the side of the master object and can be read like a double line (required lookup field). Let's use an example from program management on Nonprofit Cloud.

A Program Cohort has a related list for Program Cohort Members (the broom). A Program Cohort may have no Program Cohort Members (the empty circle).

A Program Cohort Member is a child or detail of Program Cohort (the diamond). You have to translate here to understand that Program Cohort Member has a required lookup field to Program Cohort. This is similar to the double line indicator because in Salesforce it appears as a required lookup field. However, master-detail relationships have some other effects beyond the lookup field being required. You can learn more about master-detail relationships from the Salesforce Object Relationships Overview article.

 

Summary

  • We read the diagram in this order:

  1. Object A

  2. The connection touching object B

  3. Object B. 

  • EG Object A has a lookup field to Object B. It is a required lookup field. 

  • A line closest to an object is one, or a lookup field.

  • Brooms are many, or related lists.

  • A line above another symbol is also one, or required. Double line = required lookup.

  • An empty circle above another symbol is none, or not required. A line with a circle = a lookup field that isn’t required.

  • A diamond is like double lines, a required lookup, but comes with extra functionality (master-detail relationship).

What does this mean for reporting?

When we’re building reports, referencing a diagram like the Salesforce data models allows us to understand the relationships between the objects we’re reporting on to make more informed reporting. If I use a record type that shows Contacts and Addresses, I know from the data model that not all Contacts will have an Address. Out-of-the-box, it’s not a required field. Depending on the purpose of the report, I may want a record type that gives me only Contacts with Addresses or one that gives me Contacts whether or not they have Addresses. This also assists with troubleshooting. Why is my Contact Address report not showing me all Contacts? It could be that my report type is only showing me Contacts that have Addresses.

Brooke LaPointe