Part 1: What’s a Salesforce Data Relationship?

Technically, a data relationship exists when data from one table is bound to another table through the use of specific fields and values within those fields.

Data relationships can be compared to families (we’re talking real people, not data stuff). We can have a parent (or grandparent) with a connection to their children, or indirectly to their children’s children. They are bound together by their family bonds (whatever form they take—there is still a connection). That’s one of the reasons we frequently use terms like parent, children and even siblings when discussing relationships between tables.

How do tables get ‘bonded’ together?

It works with one table having a primary key field, and another table having a foreign key field. The primary key field is typically the unique identifier for the table it lives on. The foreign key field lives on the other table. It contains the value of the primary key field for the record that is to be associated. Tables should have a primary key–it’s how each row in the table is uniquely identified.  

Let’s say we want to identify each of our customers’ super favorite desserts. This simple example is one way to meet this requirement:

One table is the list of desserts, where each is identified by a unique Id (primary key). In the second table is a list of customers, also with their own primary key unique Ids. Notice that the Customer table also has a column called “Favorite Dessert”–the foreign key–containing the corresponding Dessert Id identifying the customer’s favorite dessert, establishing a data relationship.

If we think about how these tables relate—it implies that relationships have a ‘direction’. The foreign key ‘knows’ which record on the primary key’s table it relates to, because it contains the value of the primary key. In the above example, the table that is being related to, the “Dessert table”, has no way to know what is related to it. It contains no information regarding what foreign key fields have its primary key values: it’s oblivious (we’ll get back to this in a moment).

Benefits of Relating Tables Together

The benefits of relating two different tables in this manner are tremendous. Consider a more realistic business requirement: tracking all orders for each customer. If we couldn’t leverage more than one table we could end up with something like this:

Customer First Name	Customer Last Name	Customer Street Address	Customer City	Customer Territory	Customer Postal Code	Order No.	Order Date	Order Status	Order Amount
Jane	Smith	100 Main	Mainville	TX	01112	101	3/3/2020	Shipped	$35.09
Jane	Smith	100 Main	Mainville	TX	01112	183	3/18/2020	Back order	$56.23

Above, the customer information is stored multiple times. That creates inefficiencies and can lead to confusion/data inconsistency (when the customer wants to update their info—where does that update get applied).

With two tables, the above instead can look like this:

Customer Id (Primary Key)	Customer First Name	Customer Last Name	Customer Street Address	Customer City	Customer Territory	Customer Postal Code
C4014	Jane	Smith	100 Main	Mainville	TX	01112
C8876	Mitesh	Patel	6621 Grove Street	Grange	IA	43112

Order Number	Customer Id (Foreign Key)	Order Date	Order Status	Order Amount
101	C4014	3/3/2020	Shipped	$35.09
183	C4014	3/18/2020	Back order	$56.23
201	C8876	3/15/2020	Shipped	$73.23

We can see through this simplified example, the benefit of relating tables together (note that the splitting up data into too many tables can sometimes lead to other problems; which is outside of the scope of this discussion—maybe for another time).

On the Salesforce platform, the Setup UI for creating fields streamlines the process of working with primary and foreign keys. Whenever we create a field on a Salesforce object (they aren’t referred to as tables on the platform—this will be explored shortly) that references another object that new field is a foreign key. The platform assumes that the primary key is the standard Id field on the related object for our newly created relationship field. This behavior applies to Salesforce’s master-detail, lookup and hierarchical relationship fields (these and other types of relationships will be explored in the following posts).

These relationships allow ‘traversing’ between objects, allowing us to build complex formula fields and automations that can reference an object multiple-times removed—as long as each has a relationship between each that can be followed.

3 Ways to Relate the Data

When we design a solution and are thinking about what kind of relationship field should be considered, it’s usually a good idea to identify how the data will relate to each other. There are three ways:

• One-to-one: this is where there will be a need to have one record on the foreign key table be associated with a single primary key record
• One-to-many: as covered in the orders example above, this is where the foreign key table has one or more records that are associated to a single primary key record; this is probably the most common way data is related
• Many-to-many: a complex association between two tables in that both tables need to have a single record on each table be associated with multiple records on the other; an example use case could be an aircraft reservation system—an aircraft flight can have many passengers and passengers can be on many flights
• To implement a many-to-many relationship requires an additional table—usually referred to as a ‘join’ or ‘junction’ to sit between the two tables (in the following post we’ll discuss a little more on many-to-many relationships)

It can get difficult keeping track of objects and relationships—especially as things grow. To help maintain sanity there are a couple of different ways we can visualize these relationships. Probably the most popular visualization tool is the entity relationship diagram (ERD) leveraging crow’s foot notation (there’s a ton of on-line resources that explore ERDs and crow’s foot notation—it would be time well spent!).

Salesforce offers ERDs describing the key relationships for standard objects here (https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/data_model.htm). Of course, we would be remiss in not calling out Salesforce’s Schema Builder that automatically provides a visualization of objects in a particular instance and how they relate.

Back to Basics

Let’s circle back to the idea that the primary key is ‘oblivious’ to what is related to it, that we mentioned earlier. Salesforce improves upon this in two ways:

• In the UI, through a related list of records
• Requiring that child relationships have unique names for use in queries (and process builders)  

Let’s dive into these.

Related lists are a huge benefit--think about it. Just by creating a relationship we automatically get a UI component we can add (or not add) to the screen that will display all the related records. On top of that, each child relationship requires a unique name (see https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_and_custom_objects.htm to nerd out).

Together, this gives us some real flexibility. Consider the use case where we have a custom asset object that needs to relate to two different Accounts (through two different relationship fields)—where one identifies the manufacturer and the other the distributor. This translates into having two different related lists on the Account, one for each relationship.

Side note: Why are they called objects and not tables in Salesforce? For fear of oversimplification it’s thanks to the paradigm of object-oriented programming. Object oriented programming revolves around the concept of an object which is made up of two parts:

• the data the object contains (the properties of the object) and,
• the stuff the object can do to the data (the methods).

When we think about Salesforce objects it’s evident that they each appear as a table (they have fields!). What isn’t as obvious is how the platform automatically provides us with this extra juicy goodness: sharing, security, list views, related lists, reporting, etc. These are automatically available whenever we create a new object. To close the loop: the table is the property and all that juicy goodness is the methods; hence we refer to Salesforce tables as objects.

In Part 2 of Exploring Salesforce Data Relationship Series, “Relationship Workshorses”, we’re going to build on what we just covered and explore Salesforce’s workhorse relationships: lookup, master-detail and hierarchical. It’s gonna get interesting!

 View the entire 5-part Salesforce Data Relationships series here.

Series Navigation