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 dependant 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. As of Summer ’12, a few lookup field enhancements have been added to allow for more granular control on response actions when handling deletions.
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. First, when a record of the master object is deleted, its related detail records are also deleted. Secondly, the Owner field on the detail object is not available and is automatically set to the owner of its associated master record. Third, 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.
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.