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).
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|
|Order Date||Order Status||Order Amount|
|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|
|C8876||Mitesh||Patel||6621 Grove Street||Grange||IA||43112|
|Order Number||Customer Id (Foreign Key)||Order Date||Order Status||Order Amount|
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.
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:
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.
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:
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:
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.