When two objects are related in the database, they are linked together by a relationship field. This allows you to associate related information easily between objects. All relationships maintain some form of a parent-child relationship. This can be thought of as a hierarchical structure where one object looks up to the other, and as such, you only need to create one relationship field. You create a relationship field in the child object. In order to determine the parent, you need to consider which object is dependent on the other and how many records of both objects could possibly be related to each other in your data model.
There are two main buckets for categorizing potential relationships: either a “one-to-many” or “many-to-many” relationship. Both of these describe how many children can be associated to their related parent. The two fields available for creating these kinds of relationships in the database, lookup and master-detail, have unique characteristics regarding how they handle data deletion, record ownership, security, and required fields. When deciding between these two types of relationships, you need to think about which type of field will hold the kind of functionality you are looking for.
One-to-many means there is one parent record with the potential to be related to many child records. The first way to accomplish this would be to create a lookup field. A lookup field links two objects together, but has no effect on deletion or security. This means when you define a lookup relationship, data from the child object can appear as a custom related list on page layouts for the parent object; however, you are not required to populate the lookup field on creation of a child record. Conversely, if you delete the parent record, the child record will still exist, but the field referencing the deleted record by default will be cleared.
But, You can’t delete an object or record in a lookup relationship if the combined number of records between the two linked objects is more than 100,000. To delete an object or record in a lookup relationship, first delete an appropriate number of its child records.
“As of Summer ’12, a few lookup field enhancements have been added.”
When a lookup field is optional, you can specify one of three actions to take place on dependent lookup fields when someone deletes a referenced lookup record:
- Clear the value of this field: This is the default option, available with previous releases. Setting a lookup field to null is appropriate when the field does not have to contain a value.
- Don’t allow deletion of the lookup record that’s part of a lookup relationship:
This new option restricts the deletion of a lookup record that has dependencies, such as a workflow rule built on the relationship.
- Delete this record also: This new option cascades the deletion of a referenced lookup record to dependent records. It is available only for lookup fields in a custom object; however, the lookup field can reference either a standard or custom object. Choose this option for tightly-coupled record relationships when you want to completely delete related data in one operation.
In the example below, we can see projects and team members. Team members can be a part of a project, but are not required to be a part of one. A project can have many team members, but a team member can only be a part of one project. Therefore, this type of relationship would be a lookup relationship.
The second way to create a one-to-many relationship is with a master-detail relationship field. This is when the master (parent) object controls certain behaviors of the detail (child) object.
Behaviors of master-detail relationships include:
- Deleting a detail record moves it to the Recycle Bin and leaves the master record intact;
- when a record of the master object is deleted, its related detail records are also deleted.
- The Owner field on the detail object is not available and is automatically set to the owner of its associated master record.
- Undeleting a detail record restores it, and undeleting a master record also undeletes related detail and subdetail records.
- However, if you delete a detail record and later, separately, delete its master record, you cannot undelete the detail record, as it no longer has a master record to relate to.
- By default, records can’t be reparented in master-detail relationships. Administrators can, however, allow child records in master-detail relationships on custom objects to be reparented to different parent records by selecting the Allow reparenting option in the master-detail relationship definition.
- The detail record inherits the sharing and security settings of its master record. The master-detail relationship field is required on creation of all child records.
- The master object can be a standard object, such as Account or Opportunity, or a custom object. But, We can’t create standard object as a child to custom object.
In the picture below you can see to do items are linked to team members. To do items must always be related to a team member, and if the team member were deleted, their to do items would become irrelevant. Therefore, this relationship would be a master-detail relationship.
The many-to-many relationship is a bit more complicated. A many-to-many relationship allows each record of one object to be linked to multiple records from another object and vice-versa. In this case, you need to create a junction object, which will create a unique link for every instance that you relate a parent object to a child. To build out a many-to-many relationship, create a custom junction object with two master-detail relationship fields linking to the objects you want to relate.
In the example below, we still have projects and team members with related to do items, but in this case team members can be a part of multiple projects. Projects can have several team members, and team members can be a part of several projects. Conversely, both objects are not required to be related. A team member can be a lone wolf and not participate in any projects, and a project does not need to have team members. Therefore, instead of having a lookup field linking projects and team members like the one-to-many case, we must build a junction object that will create a unique record for every instance where a team member joins a project. This junction, called project team member, is illustrated in the diagram below.
This type of relationship is a special lookup relationship available only for the user object. It allows users to use a lookup field to associate one user with another that does not directly or indirectly refer to itself. For example, you can create a custom hierarchical relationship field to store each user’s direct manager.
“A lookup relationship on the user object to the user object“
Go to setup⇒ customize⇒ user⇒ fields⇒ create new custom field.
Here you fill find hierarchy relationship instead of Master-detail and lookup.