# 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.

- Skill: `pranavnagrecha/lookup-and-relationship-design` (Agent Skill, multi-file: 7 files)
- Install (CLI): `npx skillmds add pranavnagrecha/lookup-and-relationship-design`
- Raw SKILL.md: https://api.skillmd.com/api/skills/pranavnagrecha/lookup-and-relationship-design/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: PranavNagrecha (https://skillmd.com/u/pranavnagrecha)
- Updated: 2026-09-08
- Page: https://skillmd.com/skills/pranavnagrecha/lookup-and-relationship-design

---


# 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

1. Clarify the business semantics: ownership, deletion, aggregation.
2. Count existing relationship fields on the child object (max 40).
3. Pick the type from the decision table.
4. For master-detail, verify data quality: no orphans will exist post-deployment.
5. Plan the roll-up summary fields (or Apex rollup) if aggregation required.
6. Document sharing impact — master-detail children inherit from parent.
7. 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, `relationshipOrder` names the intended parent as primary
- [ ] SOQL path depth under 5 levels for queries traversing this relationship
- [ ] Data model diagram updated

## Salesforce-Specific Gotchas

1. **Master-detail creates ownership inheritance.** Custom sharing rules on child objects with master-detail are limited; sharing follows parent.
2. **Converting lookup → master-detail requires all rows have a value.** Orphans block conversion; cleanup comes first.
3. **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 strategy
- `admin/many-to-many-relationships` — junction objects
- `apex/apex-rollup-patterns` — Apex-based rollups when master-detail unavailable