Data Modeling

Data modeling on the Salesforce platform differs from traditional relational database design in fundamental ways. The platform imposes governor limits, sharing model constraints, and relationship rules that shape every design decision. Every modeling choice cascades into security, integration, and performance.

Standard vs Custom Objects

The first decision in any data model: use what Salesforce provides, 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)
• Full control over field-level security is needed without fighting inherited permissions
• The entity has no overlap with standard object behavior
• A standard object would be polluted 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	Smooth	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.

Foundational Context

For foundational coverage of Salesforce object relationships (creating lookups, master-detail setup, relationship field configuration), see Data Modeling (Trailhead). This page focuses on the architect-level trade-offs: when each relationship type cascades into sharing, performance, and governance consequences.

Lookup vs Master-Detail

Figure 1. Five criteria drive the lookup vs master-detail decision: child independence, roll-up summary need, sharing inheritance requirements, reparenting flexibility, and cascade delete behavior. Getting this wrong after data is populated is costly to reverse.

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. Relevant 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, a junction object with two master-detail relationships is required.

Figure 2. A junction object holds two master-detail relationships, one to each parent, enabling a native many-to-many relationship on the platform. The first master-detail created determines the primary sharing model for the junction records.

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 (via Change Data Capture change event triggers only)
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 exist:

• 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 only (Async SOQL retired Summer 2023 — use Batch Apex or Bulk API)	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 fundamentally different data models call for separate objects

Record type proliferation

Every record type adds complexity to page layouts, validation rules, flows, and testing. 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.

Figure 3. Person Accounts enablement is permanent and org-wide. The decision turns on whether individuals genuinely need Account-level features such as teams, hierarchy, or entitlements, and whether existing code and integrations can absorb the change.

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 this internal structure is critical for integration and Apex development.

Figure 4. A Person Account is not a new object type. It is a dual-record structure where one Account and one Contact are created and kept in sync automatically. APIs querying Contacts will return Person Account contact records, so integrations must filter on IsPersonAccount.

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. 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 unique relationships per object (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, adding computation to every query
• Cross-object formulas create implicit dependencies that affect deployment order
• Deterministic formulas (no NOW(), TODAY()) can be indexed, which matters 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, 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

Figure 5. The classic B2B sales data model using standard Salesforce objects. Standard objects carry pre-built features (lead conversion, forecasting, case escalation) that custom objects must replicate from scratch, so this model should be the default starting point for any sales scenario.

Service Cloud Model with Knowledge

Figure 6. Service Cloud adds Entitlements, Milestones, and Knowledge Article linkage on top of the standard Account-Contact-Case model. Entitlements govern which SLA terms apply per account, while Milestones track whether response and resolution time targets are being met.

Custom Multi-Tenant Model (Common CTA Scenario)

Figure 7. A partner channel data model links Partner Accounts and their users to Deal Registrations and MDF funding requests. Deal Registrations convert to Opportunities, allowing channel revenue to be tracked through the standard sales pipeline.

B2C / Person Account Model

Figure 8. The B2C data model centers on Person Accounts, which combine Account-level features (teams, hierarchy, entitlements) with individual consumer identity. Consent and Loyalty objects are common additions in retail and financial services implementations.

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.

Figure 9. The B2B2C hybrid model requires Person Accounts enabled org-wide so both Business Accounts (B2B) and Person Accounts (B2C consumers) coexist. Record types on Account distinguish the two populations, while Storefront bridges the business seller and the consumer buyer.

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). This 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.

3. Ignoring Sharing Implications

Choosing lookup when master-detail is needed for sharing, or vice versa. A lookup relationship means the child has its own sharing model. If children must 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.

---

Related Topics

• Sharing Model: master-detail vs lookup determines sharing inheritance and OWD impact
• Field & Object Security: field-level security and object permissions constrain data model design
• Integration Patterns: object structure affects API payloads, ETL field mapping, and external ID strategy
• Large Data Volumes: relationship type and formula fields affect query performance at scale
• Data Migration: relationship order dictates migration load sequence
• Development Lifecycle: record types and custom objects affect change management and deployment complexity
• Org Strategy: data model complexity influences single-org vs multi-org decisions

---

Sources

• Salesforce Architects: Data 360 Architecture
• Salesforce Help: Object Relationships Overview
• Salesforce Help: Considerations for Object Relationships
• Salesforce Help: Big Objects Implementation Guide
• Salesforce Help: Person Accounts
• Salesforce Help: Considerations for Using Person Accounts
• Salesforce Developer: SOQL Polymorphism - TYPEOF
• Salesforce Developer: Working with Polymorphic Relationships in SOQL
• Salesforce Developer: Person Accounts API Guidelines
• Salesforce Architect: Data Model Notation
• Salesforce Architect: Customer Data Models
• Salesforce Developer: B2B Commerce Data Model
• Trailhead: B2B2C Commerce Data Model
• Trailhead: Data Modeling
• CTA Study Guide: Data Domain