Data Modeling: Quick Reference
Cheat sheet for data modeling decisions at the CTA review board. For the full deep dive, see Data Modeling and Decision Guides.
Relationship Types at a Glance
| Characteristic | Master-Detail | Lookup | External Lookup | Hierarchical |
|---|---|---|---|---|
| Cascade delete | Yes | No | No | No |
| Sharing inheritance | Yes | No | No | No |
| Roll-up summaries (native) | Yes | No | No | No |
| Child has OwnerId | No | Yes | N/A | Yes |
| Required field | Always | Optional | Optional | Optional |
| Reparenting | Off by default | Always | Always | Always |
| Max per object | 2 | 40 | 40 | 1 (User only) |
| Supports junction objects | Yes | No | No | No |
Hard Limits Cheat Sheet
| Limit | Value |
|---|---|
| Custom fields per object | up to 500 (varies by edition and field type) |
| Master-detail relationships per object | 2 |
| Total relationships per object | 40 (combining lookups and master-detail) |
| Roll-up summary fields per object | 10 (default; can be increased to 25 via Salesforce Support) |
| Objects in a single SOQL query (relationships) | 55 |
| Cross-object formula field span | 10 levels |
| Record types per object | No hard limit (practical: keep under 10) |
| Custom objects per org | 200 (EE) / 2,000 (UE/PE) |
| Big Objects per org | 100 |
| External Objects per org | 100 |
Relationship Decision Quick Test
For each relationship, ask these 5 questions:
| Question | Yes = MD | Yes = Lookup |
|---|---|---|
| Is the child meaningless without the parent? | X | |
| Do you need native roll-up summaries? | X | |
| Must child inherit parent sharing? | X | |
| Must child have its own owner? | X | |
| Need to reparent frequently? | X |
Score 3+ for MD — use master-detail. Score 3+ for Lookup — use lookup. Tie — default to master-detail (harder to add later).
ERD Patterns for Common CTA Scenarios
B2B Sales + CPQ
erDiagram
ACCOUNT ||--o{ CONTACT : "has"
ACCOUNT ||--o{ OPPORTUNITY : "owns"
OPPORTUNITY ||--o{ QUOTE : "generates"
QUOTE ||--o{ QUOTE_LINE_ITEM : "contains"
PRODUCT ||--o{ PRICEBOOK_ENTRY : "listed in"
PRICEBOOK ||--o{ PRICEBOOK_ENTRY : "contains"
PRICEBOOK_ENTRY ||--o{ QUOTE_LINE_ITEM : "prices"
ACCOUNT ||--o{ CONTRACT : "signs"
Service + Entitlements
erDiagram
ACCOUNT ||--o{ CONTACT : "has"
ACCOUNT ||--o{ ENTITLEMENT : "covered by"
CONTACT ||--o{ CASE : "submits"
ENTITLEMENT ||--o{ CASE : "governs"
CASE ||--o{ CASE_COMMENT : "has"
CASE ||--o{ EMAIL_MESSAGE : "receives"
Partner/Channel (Common CTA Scenario)
erDiagram
PARTNER_ACCOUNT ||--o{ DEAL_REGISTRATION : "submits"
DEAL_REGISTRATION ||--o{ DEAL_PRODUCT : "includes"
DEAL_REGISTRATION }o--|| OPPORTUNITY : "converts to"
PARTNER_ACCOUNT ||--o{ MDF_REQUEST : "claims"
MDF_REQUEST ||--o{ MDF_CLAIM : "itemized by"
Standard vs Custom Object Decision
| Signal | Use Standard | Use Custom |
|---|---|---|
| Built-in features needed (lead conversion, forecasting) | X | |
| AppExchange compatibility matters | X | |
| Entity maps 80%+ to standard object | X | |
| No standard object represents this concept | X | |
| Need full schema control | X | |
| Standard object would become God Object | X |
Board framing
Always say: “I evaluated standard objects first. I chose custom because [specific reason].” Never present a custom object without explaining why you rejected the standard alternative.
Junction Object Rules
- Two master-detail relationships, one to each parent
- First MD created determines primary sharing and default report type
- Both parents see related lists to the junction
- Deleting either parent deletes the junction record
- Junction can hold its own fields (dates, statuses, amounts)
Person Accounts — The Irreversible Decision
| Factor | Verdict |
|---|---|
| Pure B2C, greenfield org | Consider enabling |
| B2B + B2C hybrid, existing org | Extreme caution — audit all code and packages first |
| AppExchange packages in use | Check compatibility before deciding |
| Already in production with B2B data | Likely keep Contact model with workarounds |
Cannot be undone
Enabling Person Accounts is permanent. Once enabled, every trigger, integration, report, and AppExchange package must handle both Person and Business Accounts. Evaluate with a full impact analysis.
Reverse-Engineered Use Cases
Scenario 1: Insurance Platform — Policy Management
Situation: Insurance company needs to track Policies, Claims, and Coverage. Policies belong to Accounts. Each Policy has multiple Coverage lines and can have multiple Claims.
What you’d do: Use Account (standard) as the customer. Create custom objects: Policy__c (MD to Account), Coverage__c (MD to Policy), Claim__c (lookup to Policy — because a claim can exist in dispute without a policy). Use lookup for Claim-to-Policy because claims may be reparented during dispute resolution and need independent ownership for the claims team.
Why: MD on Coverage ensures cascade delete (coverage is meaningless without the policy) and roll-ups (total coverage amount). Lookup on Claim preserves independent sharing for the claims department.
Scenario 2: University — Student Enrollment
Situation: University tracks Students, Courses, and Enrollments. Students enroll in many courses; courses have many students.
What you’d do: Contact = Student (standard, leveraging standard features). Custom Course__c object. Junction object Enrollment__c with two MDs: first MD to Contact (Student), second MD to Course__c. Enrollment holds grade, status, enrollment date.
Why: First MD to Contact makes Contact the primary parent for sharing — student data is more sensitive than course data. Junction enables native roll-ups on both sides (total enrollments per student, total students per course).
Scenario 3: Manufacturing — Product Hierarchy
Situation: Manufacturer needs a 4-level product hierarchy: Product Family > Product Line > Product > Component. Some components are shared across products.
What you’d do: Product Family and Product Line as custom objects with MD chain. Product (standard). Component__c as custom with a junction to Product (M:N because components are shared). Avoid MD chain deeper than 3 levels for sharing model clarity.
Why: Components shared across products require M:N (junction), not a simple parent-child. Keeping the hierarchy to 3 levels of MD avoids cascading sharing complexity. Use formula fields to surface parent names down the chain instead of adding more relationships.
Common modeling mistakes
- God Object: Cramming 400+ fields into Account. Split into focused custom objects.
- Over-normalization: Too many objects with 5-10 fields each. SOQL joins are expensive.
- Wrong relationship type: Using lookup when MD is needed for sharing. Changing later requires all child records to have non-null parent values.
- Late record types: Adding record types after 500K records means backfilling every record.
- Ignoring sharing implications: The relationship type IS the sharing model decision.