---
name: salesforce-data-model
description: Design and review Salesforce data models — standard vs custom objects, lookup vs master-detail vs junction objects, record types, field design and picklists vs lookups, external Ids, formula and roll-up strategy, sharing model (OWD, roles, sharing rules, Apex/manual sharing, restriction rules), large data volume (LDV) patterns including skinny tables, indexes, skew and archiving, Big Objects, Data Cloud boundaries, and migration/ERD deliverables. Use this whenever the user asks how to model something in Salesforce, whether to create a custom object or use a standard one, which relationship type to use, how to design sharing/visibility, how to handle millions of records, or asks for an ERD, object schema, or data dictionary. Also apply it when reviewing a proposed Salesforce schema.
metadata:
  technology: Salesforce
  type: architecture
---

# Salesforce Data Model

On Salesforce the data model *is* the security model, the reporting model and half the automation model. A relationship type chosen in five minutes decides sharing behaviour, roll-up options, delete behaviour and API shape for years. Slow down here.

## 1. Standard first

Before any custom object, ask: does **Account / Contact / Lead / Opportunity / Case / Order / Product / Asset / Contract / Campaign / Individual / Person Account** already model this? Standard objects bring built-in UI, reports, automation hooks, AppExchange compatibility, and future Salesforce features for free. Extend with custom fields and record types; create a custom object only when the entity is genuinely distinct, has its own lifecycle, and needs its own sharing.

Write the decision as: *"We model X as custom object `X__c` because standard object Y would force … ; alternatives rejected: …"*

## 2. Relationship choice

| Relationship | Choose when | Consequences |
|---|---|---|
| **Master-Detail** | Child cannot exist without parent; parent's sharing should govern child; you need **roll-up summary fields** | Cascade delete; child inherits OWD/sharing; max 2 M-D per object; parent required, reparenting optional |
| **Lookup** | Child is independent or optional; different sharing needed; parent may be deleted | Own sharing; set delete behaviour (clear / block / cascade via Apex); can be required or optional |
| **Junction object** (two M-D) | Many-to-many | Primary M-D controls sharing/look & feel; roll-ups to both parents |
| **External lookup / Indirect lookup** | Related record lives in an external system (Salesforce Connect) | Read via OData/Apex adapter; no roll-ups |
| **Hierarchical** | User-to-User only | — |
| **Polymorphic** (`WhatId`, `WhoId`) | Standard activity/attachment patterns | Can't create custom polymorphic fields; use two lookups + validation or a junction |

Rule: default to **Lookup** unless you need roll-ups or inherited sharing; M-D is much harder to change later (requires all children to have a parent).

## 3. Field design

- **Picklist vs lookup to a custom object**: picklist for a stable, small, org-wide list; **Global Value Set** when shared across objects; lookup to a "reference data" object when values need attributes, owners, hierarchy, or > ~100 entries. Restricted picklists always; controlling/dependent picklists documented.
- **External Id** (unique, case-insensitive usually) on every object that syncs with another system — enables upserts and idempotent integrations.
- **Formula fields** for display-only derivations; they can't be indexed (non-selective in SOQL/reports on LDV) and count toward compile size limits.
- **Roll-up summaries** only on M-D; otherwise Flow-based roll-ups for low volume, or Apex/Batch for high volume; DLRS is fine for admins but watch limits.
- Field naming: `Label` for humans, `API_Name__c` in PascalCase with underscores; description and help text filled — treat the schema as documentation.
- Avoid text fields for dates, numbers, booleans; avoid long-text fields you'll want to filter on; keep record-level notes in Notes/ContentNote, not in a 32k text area.
- Field count: > ~300 fields on one object is a design smell — split by concern (settings object, detail object) or use record types with different page layouts only for genuine variants.

## 4. Record types

Use record types when the **same entity** has variants that differ in **picklist values, page layout, and business process** (Case: Support vs Billing). Don't use them as a substitute for a separate object (different lifecycle, different sharing) or for a single flag that a picklist field would serve.

## 5. Sharing and visibility

Design top-down; make it explicit in a table.

| Layer | Purpose |
|---|---|
| **Org-Wide Defaults** | Baseline per object: Private / Public Read Only / Public Read/Write / Controlled by Parent. Start Private for anything sensitive. |
| **Role hierarchy** | Managers see subordinates' records (unless "Grant Access Using Hierarchies" disabled for custom objects) |
| **Sharing rules** | Criteria- or owner-based, to public groups/roles — declarative, recalculated automatically |
| **Manual / Apex sharing** | Record-specific grants (`Object__Share` with `RowCause`); needed for team/territory patterns rules can't express |
| **Teams / Territories** | Account/Opportunity/Case teams; Enterprise Territory Management for geo/segment sales |
| **Restriction rules / Scoping rules** | Narrow visibility *below* what sharing grants (e.g. contractors only see their region) |
| **Permission sets / groups** | Object CRUD, FLS, tabs, Apex/VF access — assign via Permission Set Groups; profiles minimal |
| **Field-level security** | Hide sensitive fields; Shield Platform Encryption for regulatory fields (know its query limits) |

Rules of thumb: model access with **permission sets, not profiles**; never rely on hiding fields in UI as security; Apex sharing must be rebuilt on ownership change — write the recalculation job; test sharing with `System.runAs` in Apex tests.

Experience Cloud: external users use **Sharing Sets** (Customer Community) or **Sharing Rules/Groups** (Customer Community Plus/Partner) — decide the license before the model.

## 6. Large data volumes (LDV) — 1M+ rows per object

- **Selectivity**: queries/reports must filter on indexed fields (Id, Name, OwnerId, CreatedDate, SystemModStamp, RecordType, M-D, lookups, external Ids, custom indexes via support). Non-selective queries fail or time out at scale.
- **Skinny tables** (via Salesforce Support) for hot read paths with < 100 fields.
- **Ownership / lookup skew**: no single owner or parent with > 10k children (locking, sharing recalculation storms). Distribute integration-owned records across users; use a "bucket" pattern for lookups.
- **Sharing recalculation cost**: every OWD/role/sharing-rule change on an LDV object triggers recalculation — batch changes, schedule off-hours, prefer Public Read Only where acceptable.
- **Archive**: Big Objects for append-only history (define index carefully; async SOQL/limited queries), or off-platform (data lake) with a summary in Salesforce; delete via Bulk API 2.0 hard delete, purge Recycle Bin.
- **Load**: Bulk API 2.0, parallel mode with skew avoided, disable triggers/flows via bypass flags during migration, defer sharing calculation (Support-enabled), load parents before children, external Ids for upsert.
- **Storage**: plan data storage (2 KB/record typical) and file storage; attachments → Files/ContentVersion, large binaries external.

## 7. Integration-friendly modelling

- External Id per synced object; `Integration_Status__c`/`Last_Synced__c` fields when troubleshooting matters.
- Immutable natural keys don't exist — use the source system's stable Id as the external Id, never Name.
- Platform Events / CDC objects designed with a payload contract; don't put PII in events unless encrypted and necessary.
- Data Cloud (CDP) is for unified profiles and analytics; don't try to replicate its harmonisation inside core objects.

## 8. Deliverables

**ERD** (Mermaid, so it lives in the repo):
```mermaid
erDiagram
  Account ||--o{ Order__c : "has"
  Order__c ||--|{ Order_Line__c : "M-D"
  Order_Line__c }o--|| Product2 : "lookup"
```

**Data dictionary** (one row per field): Object · API Name · Label · Type · Required · Unique/External Id · Indexed · Picklist values / lookup target · Populated by (user / integration / automation) · PII class · Description.

**Sharing matrix**: Object · OWD · Role hierarchy on/off · Sharing rules · Apex sharing · External access · Notes.

**Migration/rollout notes**: load order, external Ids, bypass flags, volume estimates, recalculation windows.

## Anti-patterns to reject

- Custom object duplicating Account/Contact/Case because "we needed extra fields".
- Master-Detail chosen for convenience on an entity that must survive its parent, or that needs its own sharing.
- Text fields holding Ids/emails/dates; unrestricted picklists; multi-select picklists for anything you'll report or filter on.
- Record types as a substitute for a separate object or a single boolean.
- Public Read/Write OWD on sensitive objects "to make it work".
- Profiles with permissions instead of permission set groups.
- One integration user owning millions of records (ownership skew).
- Formula fields in report filters on LDV objects; queries on `Name LIKE '%x%'`.
- No external Ids on integrated objects; upserts keyed on Name.
- 500-field objects; 40 record types; schema with empty descriptions.