Home / admin / lookup-and-relationship-design
lookup-and-relationship-design
Designing Lookup vs Master-Detail vs Hierarchical vs External relationships: cascade delete, roll-up summaries, ownership, sharing implications, polymorphic lookups, junction objects, relationship depth limits. NOT for restricting which records a lookup can pick — use admin/lookup-filter-cross-object-patterns. NOT for a roll-up across a plain lookup — use data/roll-up-summary-alternatives.
Open the skill folder on GitHub
Lookup and Relationship Design
Activate when designing a new parent-child relationship or reviewing an existing data model for correctness. The choice between Lookup, Master-Detail, Hierarchical, and External relationships has lasting implications for sharing, reporting, deletion cascades, and migration complexity — getting it wrong often requires costly rework.
Before Starting
- Clarify ownership semantics. Does the child have its own owner, or does it inherit from parent? Master-detail forces inheritance; lookup allows independence.
- Clarify deletion semantics. Should deleting the parent delete the children? Master-detail: yes (cascade). Lookup: configurable (clear field or restrict or cascade).
- Inventory roll-up aggregates. Roll-up summary fields require master-detail (or use declarative Rollup on lookup via specific features/apps).
- Count relationship fields per object. Hard limit of 40 relationship fields; soft reporting limits lower.
Core Concepts
Master-Detail
Child record is owned by parent: inherits sharing, cascade-deletes, allows roll-up summary fields on parent. A child cannot exist without a parent. Converting master-detail → lookup is possible only when no orphans exist and no roll-ups reference the field. Max 2 master-detail relationships per object.
When an object carries two (the junction-object shape), the two parents are not interchangeable: one is primary and one is secondary, recorded in the field's Metadata API relationshipOrder (0 = primary, 1 = secondary; always 0 on a non-junction master-detail). The CustomField reference states that designation "affects delete behavior and inheritance of look and feel, and record ownership for junction objects" — a design decision, not a cosmetic one. Settle which parent should govern the junction's ownership and deletion, and set relationshipOrder deliberately.
Lookup
Independent child record; keeps its own owner and sharing. Deletion behavior configurable: "Clear the value of this field" (default), "Don't allow deletion of the parent if a child exists" (restrict), or "Delete this record also" (cascade — requires specific config). No native roll-up summary.
Hierarchical (User object only)
Self-referencing lookup on User for manager hierarchy. Drives CustomField__c sharing and "Role" reporting, but not role hierarchy — that's a separate concept.
External relationships
External Lookup (ExternalLookup), Indirect Lookup (IndirectLookup) — connect to External Objects via Salesforce Connect. Different SOQL semantics and no roll-ups.
Polymorphic lookups
Task.WhatId and Task.WhoId reference multiple object types. SOQL requires TYPEOF or WhatId.Type filters. Only supported on select standard objects.
Common Patterns
Pattern: Master-detail for true parent-child
Opportunity → OpportunityLineItem: cascading delete and roll-up of revenue. Classic.
Pattern: Lookup + restrict delete for loose coupling
Account → Asset: each has its own owner, but deleting an Account with open Assets should be blocked. Configure "Don't allow deletion of the parent."
Pattern: Lookup + Apex roll-up for aggregation
When master-detail is not viable, use a trigger-framework-based rollup (e.g., Declarative Lookup Rollup Summaries app, or custom Apex).
Decision Guidance
| Need | Relationship |
|---|---|
| Cascade delete + roll-up summary | Master-Detail |
| Child has independent owner/sharing | Lookup |
| User manager hierarchy | Hierarchical |
| Child in external system | External Lookup |
| Two possible parent object types | Polymorphic (if supported) or two lookups with validation |
Recommended Workflow
- Clarify the business semantics: ownership, deletion, aggregation.
- Count existing relationship fields on the child object (max 40).
- Pick the type from the decision table.
- For master-detail, verify data quality: no orphans will exist post-deployment.
- Plan the roll-up summary fields (or Apex rollup) if aggregation required.
- Document sharing impact — master-detail children inherit from parent.
- Add relationship to the data model diagram; review with architect.
Review Checklist
- [ ] Ownership semantics documented and match chosen type
- [ ] Deletion behavior explicit (cascade / restrict / clear)
- [ ] Relationship field count under 40 on child object
- [ ] Roll-up summaries planned where needed
- [ ] Sharing impact analyzed (especially for master-detail)
- [ ] On an object with two master-details,
relationshipOrdernames the intended parent as primary - [ ] SOQL path depth under 5 levels for queries traversing this relationship
- [ ] Data model diagram updated
Salesforce-Specific Gotchas
- Master-detail creates ownership inheritance. Custom sharing rules on child objects with master-detail are limited; sharing follows parent.
- Converting lookup → master-detail requires all rows have a value. Orphans block conversion; cleanup comes first.
- SOQL relationship queries cap at 5 levels. Deep hierarchies require multiple queries or flattened references.
Output Artifacts
| Artifact | Description |
|---|---|
| Relationship design doc | Type choice, rationale, sharing impact |
| Roll-up aggregation plan | Field list + declarative or Apex approach |
| Data model diagram | Parent-child graph with relationship types |
Related Skills
data/data-model-design-patterns— holistic data model strategyadmin/many-to-many-relationships— junction objectsapex/apex-rollup-patterns— Apex-based rollups when master-detail unavailable