Data Modeling

Data modeling on the Salesforce platform is fundamentally different from traditional relational database design. The platform imposes governor limits, sharing model constraints, and relationship rules that a CTA must navigate when designing solutions. Every modeling decision cascades into security, integration, and performance.

Standard vs Custom Objects

The first decision in any data model: use what Salesforce gives you, or build your own.

When to Use Standard Objects

Standard objects (Account, Contact, Opportunity, Case, Lead) come with built-in features that are expensive to replicate:

Pre-built relationships — Account-Contact, Account-Opportunity hierarchies
Platform features — Lead conversion, Opportunity forecasting, Case escalation, Email-to-Case
AppExchange compatibility — Most packages expect standard objects
Standard reports and dashboards — Out-of-the-box analytics
Mobile and Lightning optimizations — Standard objects have dedicated page layouts and Lightning components
Process automation — Pre-built approval processes, assignment rules, escalation rules

When to Use Custom Objects

Custom objects make sense when:

No standard object fits the business concept (e.g., Project, Inventory Item, Policy)
You need full control over field-level security without fighting inherited permissions
The entity has no overlap with standard object behavior
You need to avoid polluting a standard object with unrelated fields (e.g., cramming warehouse data into Account)

The Decision Framework

CTA exam pattern

On the review board, always start with standard objects and justify why you would NOT use them. The default posture is “standard first” — custom objects require justification.

Factor	Standard Object	Custom Object
Built-in features	Yes — lead conversion, forecasting, etc.	Must be built manually
AppExchange integration	Seamless	May require mapping
Governor limits	Slightly higher in some cases	Standard limits apply
Customization freedom	Limited by existing fields/behavior	Full control
Sharing model	May inherit complex defaults	Clean slate
Reporting	Pre-built reports available	Custom report types needed

Relationships Deep Dive

Relationships in Salesforce are not just foreign keys — they drive sharing, roll-ups, deletion behavior, and query structure. Choosing the wrong relationship type is one of the most costly data modeling mistakes.

Lookup vs Master-Detail

graph LR
    A[Need a relationship?] --> B{Is the child meaningless<br/>without the parent?}
    B -->|Yes| C{Need roll-up<br/>summaries?}
    B -->|No| D[Lookup]
    C -->|Yes| E[Master-Detail]
    C -->|No| F{Need sharing<br/>inheritance?}
    F -->|Yes| E
    F -->|No| G{Allow reparenting?}
    G -->|Yes| D
    G -->|No| E

Master-Detail Characteristics

Behavior	Detail
Cascade delete	Deleting the master deletes all detail records
Sharing inheritance	Detail inherits sharing from master — no independent sharing rules
Roll-up summaries	COUNT, SUM, MIN, MAX on detail records
Owner field	Detail record has no OwnerId — ownership follows master
Required field	Master-detail field is always required
Reparenting	Off by default; can be enabled but rarely recommended
Limit	2 master-detail relationships per object

Lookup Characteristics

Behavior	Detail
Cascade delete	No cascade — child record becomes orphaned (lookup field set to null)
Sharing	Independent sharing model — child has its own OwnerId
Roll-up summaries	Not natively supported (use triggers, DLRS, or Flow)
Required field	Optional by default; can be made required
Reparenting	Always allowed
Limit	40 total relationships per object (combining lookups and master-detail — the 2 MD relationships count toward the 40 total)

The conversion trap

You can convert a lookup to master-detail only if every child record already has the lookup field populated (no nulls). You can convert master-detail to lookup at any time, but you lose roll-ups, sharing inheritance, and cascade delete. Plan this from the start.

Hierarchical Relationships

Only available on User object. Used for approval processes, role hierarchy, and manager chains. Important for CTA scenarios involving approval routing or reporting structures.

External Lookups

Link standard/custom objects to External Objects. The relationship is based on an External ID rather than a Salesforce record ID. Critical for Salesforce Connect architectures.

Junction Objects for Many-to-Many

When two objects have a many-to-many relationship, you need a junction object with two master-detail relationships.

erDiagram
    STUDENT ||--o{ ENROLLMENT : "enrolls in"
    COURSE ||--o{ ENROLLMENT : "has enrolled"
    STUDENT {
        string Name
        string Email
        string Major
    }
    ENROLLMENT {
        date Enrollment_Date
        string Grade
        string Status
    }
    COURSE {
        string Course_Name
        string Department
        int Credits
    }

Junction Object Rules

The junction object has two master-detail relationships — each pointing to one side of the M:N
The first master-detail (created first) determines the primary relationship and sharing model
The junction object can have its own fields (enrollment date, status, grade)
Deleting either master deletes the junction record
Related lists appear on both parent objects

Which master-detail comes first?

The first master-detail on the junction object determines the primary relationship for sharing, default related list behavior, and report type structure. Choose the parent that is more important for security and reporting.

Polymorphic Lookups

Polymorphic lookups point to multiple object types. Salesforce has built-in polymorphic fields:

Field	Object	Points To
`WhoId`	Task, Event	Contact OR Lead
`WhatId`	Task, Event	Account, Opportunity, Case, Custom Objects, etc.
`OwnerId`	Most objects	User OR Queue
`RelatedToId`	EmailMessage	Multiple objects

CTA Implications

SOQL complexity — You must use TYPEOF in SOQL to handle polymorphic fields:

SELECT Id, Subject,
  TYPEOF Who
    WHEN Contact THEN FirstName, LastName, Account.Name
    WHEN Lead THEN FirstName, LastName, Company
  END
FROM Task

Reporting limitations — Polymorphic fields create complex report type requirements
Trigger design — Apex triggers must handle multiple object types in the same field
You cannot create custom polymorphic lookups — only Salesforce-defined ones exist

External Objects

External Objects represent data stored outside Salesforce (in external systems accessed via Salesforce Connect).

Feature	Standard/Custom Objects	External Objects
Data storage	On-platform	External system
Governor limits	Standard limits	Callout-based limits
SOQL	Standard SOQL	Limited SOQL subset
Triggers	Full trigger support	Limited (async)
Workflows/Flows	Full support	Limited
Relationships	Lookup, MD	External lookup, indirect lookup
Real-time data	Yes (stored)	Yes (live query)

When to use external objects

External objects are ideal when data is large, changes frequently in the external system, and is read-mostly from Salesforce. They avoid data replication but add latency and callout limits. See External Data for detailed guidance.

Big Objects

Big Objects store and manage massive datasets (billions of records) on the Salesforce platform. Two types:

Standard Big Objects — FieldHistoryArchive, etc.
Custom Big Objects — Defined with __b suffix

Big Object Characteristics

Feature	Big Objects	Standard/Custom Objects
Record count	Billions	Millions (with LDV concerns)
SOQL	Standard SOQL on indexed fields (Async SOQL is being retired as of Summer ‘25)	Standard SOQL
DML	`database.insertImmediate()`	Standard DML
Indexes	Defined at creation (immutable)	Configurable
Triggers	Not supported	Full support
Reporting	Not supported (query into custom objects)	Full support
Relationships	Cannot be child in MD	Full support

CTA Use Cases for Big Objects

Audit trail archival — Move old FieldHistoryArchive records
Historical data — Transaction history, log data, IoT telemetry
Data archival — Move aged records from standard objects to preserve performance

Record Types

Record types enable different business processes, page layouts, and picklist values on the same object.

When to Use Record Types

Different user groups need different page layouts on the same object
Different picklist values per business process (e.g., Case types for Support vs Engineering)
Different sales processes for Opportunity (e.g., New Business vs Renewal)
Different Lead conversion mappings per business unit

When NOT to Use Record Types

Just to filter list views (use list view filters instead)
When a custom field with values would suffice
When you really need separate objects (fundamentally different data models)

Record type proliferation

Every record type adds complexity to page layouts, validation rules, flows, and testing. A CTA should challenge “we need a record type for each region” requirements and suggest alternatives like custom fields or dynamic forms.

Person Accounts

Person Accounts merge Account and Contact into a single record for B2C scenarios. This is one of the most consequential data modeling decisions on the platform.

graph TD
    A[B2C Requirement?] --> B{Need Account-level<br/>features for individuals?}
    B -->|Yes| C{Already using<br/>Account-Contact model?}
    B -->|No| D[Use Contacts with<br/>a placeholder Account]
    C -->|No| E[Person Accounts<br/>may be appropriate]
    C -->|Yes| F{Can you migrate?<br/>Impact on integrations?}
    F -->|Low impact| E
    F -->|High impact| D
    E --> G[Enable Person Accounts<br/>IRREVERSIBLE]

    style G fill:#ff6b6b,color:#fff

Person Account Internal Architecture

Person Accounts are not a new object type — they are a dual-nature entity that creates both an Account record and a Contact record simultaneously. Understanding the internal structure is critical for integration and Apex development.

graph TD
    subgraph PersonAccount["Person Account (Unified View)"]
        PA[Person Account Record]
    end

    subgraph Internal["Internal Platform Structure"]
        ACC[Account Record<br/>AccountId = 001xxx]
        CON[Contact Record<br/>ContactId = 003xxx<br/>AccountId = 001xxx]
    end

    PA --> ACC
    PA --> CON
    ACC ---|"1:1 automatic sync"| CON

    subgraph AccountFields["Account-Side Fields"]
        AF1[OwnerId]
        AF2[Account Record Type]
        AF3[Account Teams]
        AF4[Hierarchy / Parent Account]
        AF5[Entitlements]
    end

    subgraph ContactFields["Contact-Side Fields"]
        CF1[FirstName / LastName]
        CF2[Email / Phone]
        CF3[MailingAddress]
        CF4[Birthdate]
        CF5[Activities WhoId]
    end

    ACC --> AccountFields
    CON --> ContactFields

    style PA fill:#4c6ef5,color:#fff
    style ACC fill:#51cf66,color:#fff
    style CON fill:#ffd43b,color:#333

Integration implications

APIs see both the Account and Contact records. An integration that queries Contacts will return Person Account contacts. An integration that creates Accounts must handle IsPersonAccount logic. SOQL queries must filter on IsPersonAccount = true/false to distinguish Person Accounts from Business Accounts.

Person Account Trade-offs

Advantage	Disadvantage
Natural B2C data model	Irreversible once enabled
Unified record for individual	Breaks Account-Contact assumptions in code
Account features for individuals (teams, hierarchy)	Many AppExchange packages incompatible
Simplifies data model for B2C	Complicates B2B+B2C hybrid orgs
Person Account record type distinguishes B2C from B2B	Integration complexity increases

Irreversibility

Enabling Person Accounts cannot be undone. This is a permanent org-level change. CTAs must evaluate this decision with extreme care, especially in orgs that serve both B2B and B2C.

Formula Fields and Limits

Formula fields are powerful but consume resources. Key limits:

Limit	Value
Formula field character limit	3,900 characters (compiled size 5,000)
Cross-object references	Up to 10 levels (spanning relationships)
Formula fields per object	No hard limit, but impacts performance
SOQL and formula interaction	Formulas cannot be used in WHERE clauses

Performance Considerations

Formula fields are calculated at read time — they add computation to every query
Cross-object formulas create implicit dependencies that affect deployment order
Deterministic formulas (no NOW(), TODAY()) can be indexed — important for LDV
Heavy formula usage on LDV objects degrades list view and report performance

Roll-Up Summary Fields

Roll-up summary fields aggregate child records in a master-detail relationship.

Available Functions

COUNT — Count of detail records
SUM — Sum of a numeric field
MIN — Minimum value
MAX — Maximum value

Limits and Alternatives

Constraint	Limit
Roll-ups per object	10 per object by default (can be increased to 25 via Salesforce Support)
Filter criteria	Supported (count WHERE Status = ‘Closed’)
Cross-object formulas	Can reference roll-up values
Supported relationships	Master-detail only (native)

For lookup relationships, alternatives include:

Declarative Lookup Roll-up Summaries (DLRS) — Open-source AppExchange package
Flow-based roll-ups — Record-triggered flows with aggregation
Apex triggers — Custom roll-up logic with bulkification
Salesforce native (beta) — Roll-up on lookup is evolving

CTA exam strategy

When presenting your data model, explicitly call out where you need roll-ups. If a relationship is a lookup but needs roll-ups, explain whether you will use DLRS, Flow, or restructure as master-detail. The trade-off discussion is what earns points.

ERD Patterns for CTA Scenarios

Classic B2B Sales Model

erDiagram
    ACCOUNT ||--o{ CONTACT : "has"
    ACCOUNT ||--o{ OPPORTUNITY : "owns"
    OPPORTUNITY ||--o{ OPPORTUNITY_LINE_ITEM : "contains"
    PRODUCT ||--o{ OPPORTUNITY_LINE_ITEM : "priced as"
    OPPORTUNITY ||--o{ QUOTE : "generates"
    ACCOUNT ||--o{ CASE : "raises"
    CONTACT ||--o{ CASE : "reports"
    ACCOUNT ||--o{ CONTRACT : "signs"
    CONTRACT ||--o{ ORDER : "fulfills"
    ORDER ||--o{ ORDER_ITEM : "contains"

Service Cloud Model with Knowledge

erDiagram
    ACCOUNT ||--o{ CONTACT : "has"
    CONTACT ||--o{ CASE : "submits"
    CASE ||--o{ CASE_COMMENT : "has"
    CASE }o--o{ KNOWLEDGE_ARTICLE : "linked to"
    CASE ||--o{ EMAIL_MESSAGE : "receives"
    ACCOUNT ||--o{ ENTITLEMENT : "covered by"
    ENTITLEMENT ||--o{ CASE : "governs"
    CASE ||--o{ MILESTONE : "tracks"

Custom Multi-Tenant Model (Common CTA Scenario)

erDiagram
    PARTNER_ACCOUNT ||--o{ PARTNER_USER : "employs"
    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"
    PARTNER_USER }o--|| USER : "linked to"

B2C / Person Account Model

erDiagram
    PERSON_ACCOUNT ||--o{ CASE : "raises"
    PERSON_ACCOUNT ||--o{ ORDER : "places"
    PERSON_ACCOUNT ||--o{ TASK : "has activities"
    ORDER ||--o{ ORDER_ITEM : "contains"
    PRODUCT ||--o{ ORDER_ITEM : "fulfilled as"
    PERSON_ACCOUNT ||--o{ CONSENT : "grants"
    PERSON_ACCOUNT ||--o{ LOYALTY_MEMBER : "enrolled as"
    LOYALTY_MEMBER ||--o{ LOYALTY_TRANSACTION : "earns/redeems"

B2B2C Hybrid Model

In a B2B2C model, a business sells through another business to reach consumers. The data model must accommodate both the B2B partner relationship and the B2C consumer relationship.

erDiagram
    BUSINESS_ACCOUNT ||--o{ CONTACT : "employs"
    BUSINESS_ACCOUNT ||--o{ CONTRACT : "signs"
    CONTRACT ||--o{ ORDER : "fulfills"
    BUSINESS_ACCOUNT ||--o{ STOREFRONT : "operates"
    STOREFRONT ||--o{ CONSUMER_ORDER : "receives"
    PERSON_ACCOUNT ||--o{ CONSUMER_ORDER : "places"
    CONSUMER_ORDER ||--o{ ORDER_ITEM : "contains"
    PRODUCT ||--o{ ORDER_ITEM : "includes"
    PERSON_ACCOUNT ||--o{ CASE : "raises"

CTA hybrid scenario

B2B2C is a common CTA scenario. The key design challenge is managing two relationship models in one org: B2B (Account-Contact) and B2C (Person Account). Person Accounts must be enabled org-wide, so both models coexist. Use record types on Account to distinguish Business Accounts from Person Accounts.

Data Modeling Anti-Patterns

1. God Object

Cramming unrelated data into a single object (usually Account or a “master” custom object). Leads to:

Field count approaching 800 limit
Confusing page layouts
Security and sharing nightmares
Deployment conflicts between teams

2. Over-Normalization

Creating too many objects with narrow responsibilities. On Salesforce, joins are expensive (SOQL limits), and every relationship adds query complexity. Balance normalization with platform realities.

Choosing lookup when master-detail is needed for sharing, or vice versa. A lookup relationship means the child has its own sharing model — if you need children to inherit parent sharing, use master-detail.

4. Late Record Type Addition

Adding record types after data is populated requires backfilling all existing records. Plan record types during initial design.

5. Circular Master-Detail

Creating A -> B -> C -> A master-detail chains. Salesforce prevents this, but architects sometimes try to work around it with mixed lookup/MD chains, creating confusing ownership and sharing models.

Cross-Domain Impact

Data modeling decisions ripple across every other domain:

Security — Master-detail vs lookup determines sharing model (Security)
Integration — Object structure affects API payloads and ETL mapping (Integration)
LDV — Relationship type affects query performance with large data (Large Data Volumes)
Migration — Relationship order dictates migration sequence (Data Migration)
Governance — Record types and objects affect change management (Development Lifecycle)