lookup-and-relationship-design
Designing Lookup vs Master-Detail vs Hierarchical vs External relationships: cascade delete, roll-up summaries, ownership, sharing implications, polymorphic lookups, relationship depth limits. NOT for record-type strategy (use data-model-design-patterns). NOT for junction object patterns (use many-to-many-relationships).
Best use case
lookup-and-relationship-design is best used when you need a repeatable AI agent workflow instead of a one-off prompt.
Designing Lookup vs Master-Detail vs Hierarchical vs External relationships: cascade delete, roll-up summaries, ownership, sharing implications, polymorphic lookups, relationship depth limits. NOT for record-type strategy (use data-model-design-patterns). NOT for junction object patterns (use many-to-many-relationships).
Teams using lookup-and-relationship-design should expect a more consistent output, faster repeated execution, less prompt rewriting.
When to use this skill
- You want a reusable workflow that can be run more than once with consistent structure.
When not to use this skill
- You only need a quick one-off answer and do not need a reusable workflow.
- You cannot install or maintain the underlying files, dependencies, or repository context.
Installation
Claude Code / Cursor / Codex
Manual Installation
- Download SKILL.md from GitHub
- Place it in
.claude/skills/lookup-and-relationship-design/SKILL.mdinside your project - Restart your AI agent — it will auto-discover the skill
How lookup-and-relationship-design Compares
| Feature / Agent | lookup-and-relationship-design | Standard Approach |
|---|---|---|
| Platform Support | Not specified | Limited / Varies |
| Context Awareness | High | Baseline |
| Installation Complexity | Unknown | N/A |
Frequently Asked Questions
What does this skill do?
Designing Lookup vs Master-Detail vs Hierarchical vs External relationships: cascade delete, roll-up summaries, ownership, sharing implications, polymorphic lookups, relationship depth limits. NOT for record-type strategy (use data-model-design-patterns). NOT for junction object patterns (use many-to-many-relationships).
Where can I find the source code?
You can find the source code on GitHub using the link provided at the top of the page.
SKILL.md Source
# 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. ### 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) - [ ] 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
Related Skills
omniscript-design-patterns
Use when designing or reviewing OmniScripts for guided experiences, step structure, branching, save/resume, and the boundary between OmniScript, Integration Procedures, DataRaptors, and custom LWCs. Triggers: 'omniscript design', 'too many steps in omniscript', 'save and resume omniscript', 'branching in omniscript', 'when should this be an integration procedure'. NOT for deep Integration Procedure or DataRaptor design when the guided interaction layer is not the main concern.
flexcard-design-patterns
Use when designing, building, or reviewing OmniStudio FlexCards — including data source selection, card states, actions, conditional visibility, flyout configuration, and child card iteration. Triggers: 'FlexCard', 'card template', 'flyout', 'card action', 'card state', 'data source', 'child card', 'conditional visibility'. NOT for OmniScript design, standalone LWC development, or Apex controller architecture outside the FlexCard context.
calculation-procedure-design
Design OmniStudio Calculation Procedures and Calculation Matrices for pricing, rating, and rules-heavy scoring. Trigger keywords: calculation procedure, calculation matrix, rating engine, pricing matrix, expression set, decision matrix, OmniStudio rules. Does NOT cover: generic Apex-only pricing code, Salesforce CPQ price rules (different product), or Flow-based decision logic.
lwc-custom-lookup
Custom lookup component in LWC — typeahead/autocomplete that searches records via Apex SOSL/SOQL, shows pills, supports keyboard navigation, and manages open/close state. Use when lightning-input-field or lightning-record-picker won't work (cross-org search, computed filters, custom result rendering). NOT for in-form lookups inside lightning-record-edit-form (use lightning-input-field) or lookup filters (use admin lookup filter config).
api-error-handling-design
Designing HTTP error classification, RFC 7807-style error payload structure, and client-side error parsing for Salesforce REST/SOAP integrations and custom Apex REST endpoints. Use when deciding which HTTP status codes to return from custom Apex REST services, how to structure error response bodies, how to classify inbound API errors as retry-safe vs non-retry-safe, or how to parse Salesforce error responses on the consumer side. NOT for retry execution mechanics or circuit breaker implementation (use retry-and-backoff-patterns). NOT for Apex exception class design (use apex-error-handling-framework). NOT for OAuth error flows (use oauth-flows-and-connected-apps).
data-model-design-patterns
Use when designing, reviewing, or troubleshooting Salesforce object relationships and field type choices — lookup vs master-detail, junction object modeling, indexing strategy, and data model anti-patterns. NOT for object creation steps (use object-creation-and-design). NOT for bulk data loading operations.
data-extension-design
Use this skill when designing, creating, or troubleshooting Marketing Cloud Data Extensions — including sendable vs. non-sendable DE selection, primary key composition, data retention configuration, Send Relationship mapping, and performance indexing. Trigger keywords: data extension, sendable DE, send relationship, DE primary key, data retention, Marketing Cloud data model, DE columns, subscriber key mapping. NOT for CRM (Sales/Service Cloud) custom object design, Marketing Cloud Connect object sync configuration, or Contact Builder attribute group architecture beyond simple relationship type selection.
solution-design-patterns
Use when selecting the right automation layer (Flow, Apex, LWC) for a new feature, reviewing an existing design for technical debt, or troubleshooting a mismatched automation architecture. Triggers: 'should I use Flow or Apex', 'declarative vs programmatic', 'which layer should handle this', 'automation design review', 'should I use LWC or standard components', 'is this over-engineered'. NOT for individual feature design (use role-specific skills), NOT for detailed Apex implementation (use apex/ skills), NOT for LWC component authoring (use lwc/ skills), NOT for Flow-specific build steps (use flow/ skills).
sla-design-and-escalation-matrix
Use when designing the SLA tier definition table, escalation matrix document, and milestone threshold configuration for a Salesforce Service Cloud implementation. Covers designing the artifact layer — tier tiers (e.g., Enterprise/Professional/Basic), response and resolution time targets, business hours mapping, milestone percentage thresholds at 50/75/90/100%, and the escalation action matrix that maps thresholds to notification targets and automated actions. Triggers: SLA design, escalation matrix, milestone thresholds, tier definition, business hours alignment, SLA enforcement design. NOT for entitlement process configuration steps (use admin/case-management-setup), NOT for escalation rule setup (use admin/escalation-rules), NOT for CPQ quoting SLAs.
knowledge-taxonomy-design
Use when designing or restructuring a Salesforce Knowledge taxonomy: Data Category Group structure, hierarchy depth, article type selection, article lifecycle governance (Draft/Published/Archived), Validation Status gating, and content gap analysis via KCS methodology and Search Activity Gaps. Triggers: knowledge taxonomy design, data category hierarchy, knowledge article lifecycle, knowledge governance model, KCS content gap analysis, search activity gaps, knowledge category structure. NOT for Knowledge admin feature setup (use admin/knowledge-setup), NOT for Experience Cloud search configuration, NOT for Einstein Article Recommendations tuning.
integration-framework-design
Use when designing a reusable integration layer in Salesforce that serves multiple external APIs through a shared callout infrastructure. Triggers: 'how to design a reusable integration layer in Salesforce', 'architect an Apex callout framework for multiple APIs', 'create a centralized error handling pattern for integrations', 'service interface pattern for external APIs', 'factory pattern for dynamic API resolution', 'centralized callout dispatcher'. NOT for individual API implementation (use apex/callouts-and-http-integrations), NOT for Named Credential setup (use integration/named-credentials-setup), NOT for async callout patterns (use apex/continuation-callouts).
apex-soql-relationship-queries
Use this skill when writing or debugging SOQL relationship queries in Apex — child-to-parent dot notation traversal, parent-to-child subqueries, and polymorphic TYPEOF lookups. Trigger keywords: relationship query, subquery, dot notation, getSObjects, TYPEOF, WhatId, WhoId. NOT for aggregate queries (use apex-aggregate-queries), NOT for SOSL text search, NOT for Bulk API data loads (subqueries unsupported there).