sf-data-modeling

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

Business Need	Use Standard Object	Not Custom
Customer companies	Account	Company__c
Individual contacts	Contact	Person__c
Sales deals	Opportunity	Deal__c
Support tickets	Case	Ticket__c
Events/meetings	Event	Meeting__c
Tasks/to-dos	Task	Todo__c
Products/pricing	Product2, PricebookEntry	Product__c
Orders	Order, OrderItem	PurchaseOrder__c

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

Relationship	Cascade Delete	Roll-Up Summary	Required	Sharing Inherited
Lookup	No (configurable)	No	No	No
Master-Detail	Yes	Yes	Yes	Yes
Many-to-Many (Junction)	Both sides	From junction	Both	From primary master
Hierarchical	No	No	No	No
External Lookup	No	No	No	No

*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

Field Type	Use When	Avoid When
Text (255)	Short single-line text	Long descriptions
Long Text Area	Up to 131,072 chars	Need to filter/search on it
Rich Text Area	HTML-formatted content	Need to query/filter by content
Number	Integers, no currency	Financial values (use Currency)
Currency	Monetary values	Non-financial numbers
Date	Date without time	Need time zone info
DateTime	Timestamps, audit trails	Simple date records
Checkbox	Boolean yes/no	Optional boolean (use Picklist)
Picklist (single)	Controlled vocabulary	Many values (use Lookup)
Picklist (multi)	Multiple selections	Filtering/reporting (anti-pattern)
Formula	Calculated, read-only	Values needing DML update
Roll-Up Summary	Aggregate child data	25 per object (default limit)
External ID	Upsert key from external system	-

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

Feature	Custom Metadata	Custom Settings	Custom Labels
Deployable	Yes	No (hierarchy)/Yes (list)	Yes
Per-user/profile values	No	Yes (hierarchy)	No
Governor limit on reads	No (cached)	Yes (SOQL equivalent)	No
Best for	Config deployed with code	User/profile-specific settings	Translatable strings

// 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)

OWD Setting	Other Users	Best For
Public Read/Write	Read + Write	Reference/config data
Public Read Only	Read only	Products, pricebooks
Private	None	Accounts, Opportunities
Controlled by Parent	Inherits	Master-Detail children

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

Adapter	Use When
OData 2.0/4.0	External REST API with OData support
Custom Adapter	Proprietary API or database
Cross-Org	Another Salesforce org

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

---

Anti-Patterns

Anti-Pattern	Fix
Polymorphic lookup abuse	Use explicit lookup fields per related object
Over-normalization	Flatten into fields unless multiple addresses per record
Too many custom fields (800 limit)	Split into related child objects
Circular Master-Detail	Break the circle with a Lookup on one side
Text instead of Lookup	Use Lookup fields for referential integrity
Ignoring LDV on 100K+ objects	Request custom indexes, use skinny tables

---

Related

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