Salesforce Data Modeling

@../_reference/DATA_MODELING.md

When to Use

• Designing new custom objects, fields, or relationships
• Deciding between lookup vs master-detail relationships
• Planning record types, page layouts, or sharing model architecture
• Architecting for large data volumes (LDV) requiring index-aware field design
• Reviewing or refactoring an existing data model
• Evaluating external objects, custom metadata types, or hierarchical settings

Object Design Principles

Extend Standard Objects When Possible

Standard objects come with built-in reports, process automations, and integrations.

Custom Object Naming

API Name:    ProjectTask__c           (PascalCase + __c)
Label:       Project Task             (human-readable)
Plural:      Project Tasks
Relationship Name: ProjectTasks       (plural for child relationship)

Relationship Types

*Master-Detail can be reparented if "Allow Reparenting" is enabled.

When to Use Lookup

• Child can exist independently (Contact without Account)
• No roll-up summaries needed
• Child needs its own sharing settings
• Multiple lookups, none is primary

When to Use Master-Detail

• Child has no meaning without parent (Order Line Item without Order)
• Roll-up summary fields needed on parent
• Child deleted when parent deleted
• Child sharing inherits from parent

<!-- Master-Detail field metadata -->
<fields>
    <fullName>Project__c</fullName>
    <label>Project</label>
    <type>MasterDetail</type>
    <referenceTo>Project__c</referenceTo>
    <relationshipLabel>Project Tasks</relationshipLabel>
    <relationshipName>ProjectTasks</relationshipName>
    <relationshipOrder>0</relationshipOrder>
    <reparentableMasterDetail>false</reparentableMasterDetail>
</fields>

Field Type Guide

Multi-Select Picklist Warning

// Limited SOQL support — can use INCLUDES/EXCLUDES but not = or IN
List<Case> cases = [
    SELECT Id FROM Case
    WHERE Tag_List__c INCLUDES ('Billing', 'Technical')
];
// Cannot use in GROUP BY, ORDER BY, or most aggregates
// Consider Lookup to a Tags junction object for complex tagging

Custom Metadata Types vs Custom Settings vs Custom Labels

// Custom Metadata — no SOQL limits, deployable
String endpoint = Service_Config__mdt.getInstance('Production').Endpoint_URL__c;

// Custom Setting — profile-specific
Boolean isEnabled = Integration_Settings__c.getInstance().Is_Enabled__c;

// Custom Label — translatable
String welcomeMsg = System.Label.Welcome_Message;

Record Types

When to Use

• Different page layouts per user group
• Different picklist values per business process
• Different automation processes per record type

When NOT to Use

• Simple field-level differences (use conditional visibility)
• Access control (use sharing rules or permission sets)
• Fundamentally different data structures (use separate objects)

Id caseRecordTypeId = Schema.SObjectType.Case.getRecordTypeInfosByDeveloperName()
    .get('Internal_Support').getRecordTypeId();

List<Case> internalCases = [
    SELECT Id, Subject FROM Case
    WHERE RecordTypeId = :caseRecordTypeId WITH USER_MODE
];

Sharing Model Design

Object-Wide Defaults (OWD)

Start with Private OWD for sensitive objects and open up with sharing rules.

Apex Managed Sharing

public with sharing class ProjectSharingService {
    public static void shareProjectWithUser(Id projectId, Id userId, String accessLevel) {
        Project__Share shareRecord = new Project__Share(
            ParentId = projectId,
            UserOrGroupId = userId,
            AccessLevel = accessLevel,
            RowCause = Schema.Project__Share.RowCause.Manual
        );
        Database.SaveResult result = Database.insert(shareRecord, false);
        if (!result.isSuccess() &&
            result.getErrors()[0].getStatusCode() != StatusCode.FIELD_FILTER_VALIDATION_EXCEPTION) {
            throw new SharingException('Failed to share: ' + result.getErrors()[0].getMessage());
        }
    }
    public class SharingException extends Exception {}
}

Large Data Volume (LDV) Considerations

Objects with >100,000 records require special attention.

Schema design:

• Add external ID fields on objects queried by non-Id values
• Request custom indexes on fields used in WHERE clauses
• Consider skinny tables for frequently-accessed field subsets
• Avoid Roll-Up Summary fields on LDV child objects (use batch triggers instead)

// Good — uses indexed fields, selective
List<Order__c> orders = [
    SELECT Id, Status__c FROM Order__c
    WHERE AccountId = :accountId
      AND CreatedDate >= :thirtyDaysAgo
    LIMIT 200
];

Archiving: Move old records to BigObjects or external archive. Use batch jobs for archive-and-delete.

Junction Object Patterns

Account <-- AccountContactRelation --> Contact
             + Role (picklist)
             + IsPrimary (checkbox)
             + StartDate (date)

• Junction objects with Master-Detail on both sides inherit sharing from BOTH parents
• Add meaningful fields to the junction (Role, Start Date, Status)
• Use AccountContactRelation (standard) before creating custom junctions for Account-Contact

External Object Patterns

External Objects have no triggers, Flows, or Validation Rules. Use Apex callouts for write operations.

Anti-Patterns

Related

• Agent: sf-architect — For interactive, in-depth guidance
• Constraints: sf-apex-constraints — Governor limits and Apex safety rules