4.3 Lookup, Master-Detail, and Junction Relationships

Relationship Decisions

Relationships make the Salesforce data model useful. Without relationships, an admin only has separate lists of records. With relationships, Accounts can show Contacts, Cases can point to Assets, custom Projects can contain Milestones, and reports can answer questions across business processes. The main admin decision is whether the relationship should be loose, tight, or many-to-many.

A lookup relationship links one record to another while allowing the child record to retain more independence. The child can usually have its own owner, sharing, and lifecycle. Lookup fields can be optional or required, depending on configuration and business rules. Lookup is a strong fit when the child can exist without the parent or when the parent should not control the child record's access and deletion behavior.

A master-detail relationship is more tightly coupled. The detail record depends on the master record. Detail records do not have an independent owner in the same way as standalone records, and access to detail records is controlled by the master. Deleting the master deletes related detail records. Native roll-up summary fields become available on the master because Salesforce can trust that tight parent-child structure.

Because relationship type affects security and data lifecycle, it is not just a reporting choice. Suppose a custom Expense record belongs to a Project. If expenses should only be visible to users who can see the project, and deleting the project should remove its expenses, master-detail may fit. If expenses can be reassigned, owned by finance, or retained after a project is archived, lookup may be safer.

Lookup filters add another layer. A lookup filter limits which parent records are valid choices. For example, a Case lookup to Asset can be filtered so users select only assets related to the Case account. Filters improve data quality and user experience, but they do not replace object permissions or sharing. A user still needs access to the target records and fields.

Junction objects model many-to-many relationships. A common pattern is Student, Course, and Enrollment. A student can enroll in many courses, and a course can have many students. The Enrollment object sits between them and stores relationship-specific fields such as enrollment date, status, grade, or attendance. Without the junction object, those relationship details have no clean home.

A junction object often uses two master-detail relationships, especially when the connection should be tightly dependent on both parent records. In some designs, lookup relationships may be chosen instead if the junction record needs more independence. The admin should evaluate deletion behavior, ownership, sharing, reporting, roll-ups, and whether the connection record has its own lifecycle.

Relationship conversion is another exam area. A lookup may be convertible to master-detail only when the data and configuration meet the requirements, such as existing child records having parent values. A master-detail relationship may have dependencies such as roll-up summary fields that must be addressed before conversion. The safest admin practice is to decide relationship type carefully before loading production data.

Relationships also affect reporting. Standard and custom report types expose related records based on relationship paths. If executives need Projects with or without Milestones, the report type and relationship design must support that question. A master-detail relationship can make roll-ups easy, but a lookup relationship may be better when optional related records still need to appear in reports.

Automation uses relationships heavily. Flow can read parent fields, update related records, or enforce rules across records. However, automation should not be used to hide a poor relationship choice unless there is a clear reason. If every save requires complex flow logic to simulate ownership, roll-ups, or cascade behavior, the relationship model may need to be revisited.

A practical relationship checklist:

• Name the parent and child records in plain business language.
• Decide whether the child can exist without the parent.
• Decide who owns the child and who should see it.
• Decide what should happen when the parent is deleted.
• Identify roll-up, report, and automation needs.
• Test sample records with realistic profiles and sharing.
• Document why lookup or master-detail was selected.

The common trap is choosing master-detail only because a roll-up is wanted. Roll-ups are valuable, but they are not the only requirement. If the child needs independent access, ownership, or survival after parent deletion, forcing master-detail can create operational problems. Conversely, choosing lookup for everything can lead to unnecessary automation when the business truly has a dependent detail record.