Design Salesforce data models for Spring '26 — standard vs custom objects, lookup vs master-detail vs junction, record types, field design, config data in Custom Metadata vs Custom Settings, external Ids, formulas and roll-ups, the sharing design (OWD, role hierarchy, sharing rules, Apex sharing, restriction rules, Experience Cloud licences), data classification, large data volumes with skinny tables, indexes, skew and Big Object archiving, the Data 360 boundary, and ERD/data-dictionary deliverables.
When agents use itUse this whenever the user asks how to model something in Salesforce, whether to use a standard object or create `X__c`, which relationship type to pick, how to design visibility and sharing, where feature flags or config values should live, how to handle millions of records or a query that times out, or asks for an ERD, object schema, sharing matrix or data dictionary. Also apply it when an Apex or Flow problem turns out to be a modelling problem, such as a roll-up that needs master-detail.
Install
Copilot (VS Code, Visual Studio, Copilot CLI and github.com) reads skills from the repository — commit them so the whole team gets them.
npx skills add AGCO-Global/org-skills --skill salesforce-data-model -a github-copilot
# or with the org installer (adds .github/skills/salesforce-data-model):
npx -y github:AGCO-Global/org-skills add skill salesforce-data-model
Uses the open skills CLI. Works with Claude Code, Codex, Cursor, Copilot, Gemini CLI, OpenCode, Windsurf and 60+ others — it asks which agent to install into.
Installs the salesforce-data-model-skills plugin, which bundles all Salesforce / Data Model skills and keeps them updated.
/plugin marketplace add AGCO-Global/org-skills
/plugin install salesforce-data-model-skills@org-skills
# or just this skill, in this repository:
npx skills add AGCO-Global/org-skills --skill salesforce-data-model -a claude-code
Use Download .zip above, then upload it under Customize → Skills → Upload skill. Team and Enterprise admins can sync this repo as a plugin marketplace instead.
Installs into .agents/skills/, which Codex reads.
npx skills add AGCO-Global/org-skills --skill salesforce-data-model -a codex
Installs into Cursor's skills folder.
npx skills add AGCO-Global/org-skills --skill salesforce-data-model -a cursor
Installs into Gemini CLI's skills folder.
npx skills add AGCO-Global/org-skills --skill salesforce-data-model -a gemini-cli
Nothing to install. Press Download .zip above, unzip it, and drop the salesforce-data-model folder into your tool's skills folder — .claude/skills/, .github/skills/, .cursor/skills/ or .agents/skills/. Step-by-step, per tool: How to use it. For any other tool with rules or custom prompts, Copy SKILL.md and paste it in — it is plain Markdown.
The npx commands need Node.js 20+ and use your normal git sign-in to GitHub, so they work while the repository is private. Without Node.js, see the No Node.js tab — a skill is a folder of Markdown, so copying it in works just as well.
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, and master-detail cannot be undone once children exist. Slow down here, and write the model down.
The entity has its own lifecycle, sharing and reporting needs that a standard object would distort; say in one sentence what standard object was rejected and why
Relationship type?
Lookup
Roll-up summary needed, or the child must inherit the parent's sharing and die with it → master-detail (max 2 per object); many-to-many → junction object with two master-details
Variants of one entity?
Record types + page layouts — and the org gives every object at least one
Different lifecycle, sharing or integrations → separate object; one boolean's worth of difference → a field
Where do config values live?
Custom Metadata Type (deployable, queryable, no DML needed in tests)
Per-user/profile/org hierarchy or runtime-writable → Custom Setting; end users must report on or share it → custom object
Baseline visibility?
Private OWD, opened with sharing rules
Reference data everyone reads → Public Read Only; Controlled by Parent on master-detail children
Who gets permissions?
Permission sets and permission set groups for end users
The org additionally requires FLS on the four admin profiles for every new field — see salesforce-org-conventions
Object over 1 M rows?
Design for selectivity from day one — indexed filters, no skew
Over ~50 M or append-only history → archive to a Big Object or off-platform
Unified profiles, segmentation, AI grounding?
Data 360 (formerly Data Cloud), not more core objects
Transactional truth, sharing and automation stay in core objects
2. Relationships and their consequences
Relationship
Choose when
Consequences
Master-detail
Child cannot exist without the parent; parent's sharing governs it; you need roll-up summary fields
Cascade delete; child has no OWD of its own; max 2 per object; reparenting is optional and off by default; converting to lookup later needs every child to have a parent
Lookup
Child is independent or optional; different sharing; parent may be deleted
Own sharing; choose the delete behaviour (clear, don't allow, cascade via Apex); required or optional
Junction (two master-details)
Many-to-many
The first master-detail is primary: it controls the junction's look, feel and sharing
Hierarchical
User → User only
—
External / indirect lookup
The related record lives in another system via Salesforce Connect
Read through the adapter; no roll-ups; indirect lookup matches on an external Id
Polymorphic (WhatId, WhoId)
Standard activity and attachment patterns
You cannot create custom polymorphic fields — use two lookups plus a validation rule, or a junction
Roll-ups: master-detail gives them free. Otherwise use a record-triggered flow for low volume, Apex/Batch for high volume, and expect to write the recalculation job that repairs drift.
3. Fields, config and classification
External Id (unique, usually case-insensitive) on every object an integration writes. It makes upserts idempotent and removes duplicate hunting; it also earns a custom index automatically. Use the source system's stable key, never Name.
Picklist vs lookup: restricted picklist (or a Global Value Set when shared across objects) for a small stable list; a reference-data object once the values need attributes, owners, hierarchy or more than ~100 entries.
Formula fields are display derivations: not indexed, non-selective in SOQL and report filters, and they count against compile size. Never filter an LDV report on one.
Data classification is part of field design, not an afterthought: set Compliance Categorization (complianceGroup), Data Sensitivity Level (securityClassification), Field Usage (businessStatus) and Data Owner on every field carrying personal or regulated data. They are deployable metadata and queryable: SELECT QualifiedApiName, SecurityClassification, ComplianceGroup, BusinessStatus FROM FieldDefinition WHERE EntityDefinition.QualifiedApiName = 'Contact'. The data dictionary's PII column comes from here.
No text fields holding dates, numbers, booleans or Ids; no multi-select picklist on anything you will filter or report on; fill description and help text — the schema is the documentation.
Over ~300 fields on one object is a design smell: split by concern or push detail to a child object.
4. Sharing design
Design top-down — OWD, then role hierarchy, then sharing rules, then manual/Apex sharing, then restriction rules to narrow — and hand back a sharing matrix, not prose. The layer table, the Experience Cloud licence decision (Sharing Sets vs sharing rules), Apex sharing with RowCause, recalculation cost and User Access Policies are in references/sharing-model.md.
Three rules that decide most designs: model access with permission set groups rather than profiles; never treat a hidden field as a security control (the API returns it); and every Apex-managed share needs a recalculation job, because ownership changes wipe manual shares. Runtime enforcement in code — with sharing, USER_MODE, stripInaccessible — belongs to salesforce-security.
5. Large data volumes
Above roughly a million rows, only selective filters work: Id, Name, OwnerId, CreatedDate, SystemModStamp, RecordTypeId, master-detail and lookup fields, external Ids and Support-created custom indexes. Keep no single user owning or parenting more than ~10 000 records (ownership and lookup skew cause row-lock contention and sharing-recalculation storms).
Archive append-only history to Big Objects: the composite index is chosen at creation and cannot be changed afterwards, and queries must filter its fields left to right. Query them with SOQL on index fields, Batch Apex or Bulk API 2.0 — Async SOQL was retired in Summer '23. Skinny tables, index requests, load strategy and bypass flags are in references/large-data-volumes.md.
6. The Data 360 boundary
Store
Holds
Rule
Core objects
Transactional truth, sharing, automation, user-facing records
One-way rule: harmonise in Data 360, act in core. Write results back only through Data Actions or an enrichment field your team owns, never by syncing DMOs into core objects. Replicating rarely-changing reference data on a schedule beats virtualising it.
7. Deliverable format
## Decision — standard vs custom, relationship types, with the rejected alternative
## ERD — Mermaid erDiagram in the repo
## Data dictionary — Object · API name · Type · Required · Unique/External Id · Indexed ·
## Picklist/lookup target · Populated by · Sensitivity + compliance · Description
## Sharing matrix — Object · OWD · Hierarchy · Sharing rules · Apex sharing · External users
## Volume & rollout — rows/year, indexes, load order, bypass flags, archive plan
Filled examples of all four — Mermaid ERD, dictionary rows and a three-object sharing matrix — are in references/erd-and-dictionary.md.
Checklist
Every custom object names the standard object it replaces and why
Each relationship states roll-up and sharing consequences; master-detail is deliberate
External Id on every integrated object; upserts keyed on it, never on Name
Config values placed per the Custom Metadata / Custom Setting / object rule
Data classification set on personal and regulated fields; dictionary PII column filled
Experience Cloud licence chosen before the sharing design
LDV objects have selective filters, no owner/lookup skew, an index plan and an archive plan
Big Object index is right the first time — it cannot be changed
ERD and data dictionary committed to the repo, not in a slide
Anti-patterns
Custom object duplicating Account/Contact/Case "because we needed extra fields" → custom fields and record types on the standard object.
Master-detail for convenience → lookup unless the child truly dies with the parent or needs roll-ups.
Record types standing in for a separate object → separate object when lifecycle or sharing differs. (Every object still carries at least one record type — see salesforce-org-conventions.)
Feature flags in a Custom Setting → Custom Metadata Type; Custom Setting only for per-user/hierarchy or runtime-written values.
Public Read/Write OWD "to make it work" → Private plus sharing rules; restriction rules to narrow.
End-user permissions on profiles → permission set groups. (The org's standing exception is FLS on the four admin profiles, which every new field gets.)
One integration user owning millions of records → distribute ownership, avoid skew.
Async SOQL for Big Object reporting → Batch Apex or Bulk API 2.0 query.
Upserting on Name → external Id.
"Let's copy the Data 360 DMOs into core objects" → act in core, harmonise in Data 360.
Go deeper
references/sharing-model.md — the layer-by-layer sharing table, Apex sharing and recalculation, Experience Cloud licences, User Access Policies.
references/large-data-volumes.md — selectivity and indexes, skew, skinny tables, Big Object archiving, migration loads.
references/erd-and-dictionary.md — worked Mermaid ERD, data dictionary rows with classification, sharing matrix.
Sibling skills: salesforce-security for enforcing access in code, salesforce-integration for external Ids and sync contracts, apex-development for roll-up and query code, salesforce-automation for automation on the model, salesforce-code-review for pull requests, salesforce-org-conventions for the org's naming and mandated patterns.
---
name: salesforce-data-model
description: >
Design Salesforce data models for Spring '26 — standard vs custom objects, lookup vs master-detail vs junction, record types, field design, config data in Custom Metadata vs Custom Settings, external Ids, formulas and roll-ups, the sharing design (OWD, role hierarchy, sharing rules, Apex sharing, restriction rules, Experience Cloud licences), data classification, large data volumes with skinny tables, indexes, skew and Big Object archiving, the Data 360 boundary, and ERD/data-dictionary deliverables. Use this whenever the user asks how to model something in Salesforce, whether to use a standard object or create `X__c`, which relationship type to pick, how to design visibility and sharing, where feature flags or config values should live, how to handle millions of records or a query that times out, or asks for an ERD, object schema, sharing matrix or data dictionary. Also apply it when an Apex or Flow problem turns out to be a modelling problem, such as a roll-up that needs master-detail.
metadata:
technology: Salesforce
type: architecture
---
# Salesforce Data Model
> **Targets:** Salesforce Spring '26 (API 66.0) · **Verified:** 2026-09 against https://help.salesforce.com (sharing, data classification, Big Objects), https://developer.salesforce.com (SOQL and Metadata API) and https://architect.salesforce.com
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, and master-detail cannot be undone once children exist. Slow down here, and write the model down.
## 1. Decide first
| Question | Default | Change when |
|---|---|---|
| Standard or custom object? | **Standard** — Account, Contact, Lead, Opportunity, Case, Order, Product2, Asset, Contract, Campaign, Individual | The entity has its own lifecycle, sharing and reporting needs that a standard object would distort; say in one sentence what standard object was rejected and why |
| Relationship type? | **Lookup** | Roll-up summary needed, or the child must inherit the parent's sharing and die with it → **master-detail** (max 2 per object); many-to-many → junction object with two master-details |
| Variants of one entity? | Record types + page layouts — and the org gives **every object at least one** | Different lifecycle, sharing or integrations → separate object; one boolean's worth of difference → a field |
| Where do config values live? | **Custom Metadata Type** (deployable, queryable, no DML needed in tests) | Per-user/profile/org hierarchy or runtime-writable → **Custom Setting**; end users must report on or share it → custom object |
| Baseline visibility? | **Private** OWD, opened with sharing rules | Reference data everyone reads → Public Read Only; Controlled by Parent on master-detail children |
| Who gets permissions? | **Permission sets and permission set groups** for end users | The org additionally requires FLS on the four admin profiles for every new field — see `salesforce-org-conventions` |
| Object over 1 M rows? | Design for selectivity from day one — indexed filters, no skew | Over ~50 M or append-only history → archive to a **Big Object** or off-platform |
| Unified profiles, segmentation, AI grounding? | **Data 360** (formerly Data Cloud), not more core objects | Transactional truth, sharing and automation stay in core objects |
## 2. Relationships and their consequences
| Relationship | Choose when | Consequences |
|---|---|---|
| **Master-detail** | Child cannot exist without the parent; parent's sharing governs it; you need roll-up summary fields | Cascade delete; child has no OWD of its own; max 2 per object; reparenting is optional and off by default; converting to lookup later needs every child to have a parent |
| **Lookup** | Child is independent or optional; different sharing; parent may be deleted | Own sharing; choose the delete behaviour (clear, don't allow, cascade via Apex); required or optional |
| **Junction** (two master-details) | Many-to-many | The **first** master-detail is primary: it controls the junction's look, feel and sharing |
| **Hierarchical** | User → User only | — |
| **External / indirect lookup** | The related record lives in another system via Salesforce Connect | Read through the adapter; no roll-ups; indirect lookup matches on an external Id |
| **Polymorphic** (`WhatId`, `WhoId`) | Standard activity and attachment patterns | You cannot create custom polymorphic fields — use two lookups plus a validation rule, or a junction |
Roll-ups: master-detail gives them free. Otherwise use a record-triggered flow for low volume, Apex/Batch for high volume, and expect to write the recalculation job that repairs drift.
## 3. Fields, config and classification
- **External Id** (unique, usually case-insensitive) on every object an integration writes. It makes upserts idempotent and removes duplicate hunting; it also earns a custom index automatically. Use the source system's stable key, never Name.
- **Picklist vs lookup**: restricted picklist (or a Global Value Set when shared across objects) for a small stable list; a reference-data object once the values need attributes, owners, hierarchy or more than ~100 entries.
- **Formula fields** are display derivations: not indexed, non-selective in SOQL and report filters, and they count against compile size. Never filter an LDV report on one.
- **Data classification is part of field design**, not an afterthought: set *Compliance Categorization* (`complianceGroup`), *Data Sensitivity Level* (`securityClassification`), *Field Usage* (`businessStatus`) and *Data Owner* on every field carrying personal or regulated data. They are deployable metadata and queryable: `SELECT QualifiedApiName, SecurityClassification, ComplianceGroup, BusinessStatus FROM FieldDefinition WHERE EntityDefinition.QualifiedApiName = 'Contact'`. The data dictionary's PII column comes from here.
- No text fields holding dates, numbers, booleans or Ids; no multi-select picklist on anything you will filter or report on; fill description and help text — the schema is the documentation.
- Over ~300 fields on one object is a design smell: split by concern or push detail to a child object.
## 4. Sharing design
Design top-down — OWD, then role hierarchy, then sharing rules, then manual/Apex sharing, then restriction rules to narrow — and hand back a sharing matrix, not prose. The layer table, the Experience Cloud licence decision (Sharing Sets vs sharing rules), Apex sharing with `RowCause`, recalculation cost and User Access Policies are in `references/sharing-model.md`.
Three rules that decide most designs: model access with permission set groups rather than profiles; never treat a hidden field as a security control (the API returns it); and every Apex-managed share needs a recalculation job, because ownership changes wipe manual shares. Runtime enforcement in code — `with sharing`, `USER_MODE`, `stripInaccessible` — belongs to `salesforce-security`.
## 5. Large data volumes
Above roughly a million rows, only selective filters work: Id, Name, OwnerId, CreatedDate, SystemModStamp, RecordTypeId, master-detail and lookup fields, external Ids and Support-created custom indexes. Keep no single user owning or parenting more than ~10 000 records (ownership and lookup skew cause row-lock contention and sharing-recalculation storms).
Archive append-only history to **Big Objects**: the composite index is chosen at creation and **cannot be changed afterwards**, and queries must filter its fields left to right. Query them with SOQL on index fields, **Batch Apex or Bulk API 2.0 — Async SOQL was retired in Summer '23**. Skinny tables, index requests, load strategy and bypass flags are in `references/large-data-volumes.md`.
## 6. The Data 360 boundary
| Store | Holds | Rule |
|---|---|---|
| **Core objects** | Transactional truth, sharing, automation, user-facing records | Everything acted on by Flow, Apex or users |
| **Data 360 DMOs** (formerly Data Cloud) | Unified profiles, harmonised external data, calculated insights, segments, Agentforce grounding | Read-heavy, analytical, cross-system |
| **Big Objects** | Append-only history and archive | No sharing, no automation, index-only queries |
| **External objects** (Salesforce Connect) | Data that stays in the source system | Virtualised reads; no roll-ups, limited reporting |
One-way rule: **harmonise in Data 360, act in core.** Write results back only through Data Actions or an enrichment field your team owns, never by syncing DMOs into core objects. Replicating rarely-changing reference data on a schedule beats virtualising it.
## 7. Deliverable format
```
## Decision — standard vs custom, relationship types, with the rejected alternative
## ERD — Mermaid erDiagram in the repo
## Data dictionary — Object · API name · Type · Required · Unique/External Id · Indexed ·
## Picklist/lookup target · Populated by · Sensitivity + compliance · Description
## Sharing matrix — Object · OWD · Hierarchy · Sharing rules · Apex sharing · External users
## Volume & rollout — rows/year, indexes, load order, bypass flags, archive plan
```
Filled examples of all four — Mermaid ERD, dictionary rows and a three-object sharing matrix — are in `references/erd-and-dictionary.md`.
## Checklist
- [ ] Every custom object names the standard object it replaces and why
- [ ] Each relationship states roll-up and sharing consequences; master-detail is deliberate
- [ ] External Id on every integrated object; upserts keyed on it, never on Name
- [ ] Config values placed per the Custom Metadata / Custom Setting / object rule
- [ ] Data classification set on personal and regulated fields; dictionary PII column filled
- [ ] Sharing matrix covers OWD, hierarchy, rules, Apex sharing, restriction rules and external users
- [ ] Experience Cloud licence chosen before the sharing design
- [ ] LDV objects have selective filters, no owner/lookup skew, an index plan and an archive plan
- [ ] Big Object index is right the first time — it cannot be changed
- [ ] ERD and data dictionary committed to the repo, not in a slide
## Anti-patterns
- **Custom object duplicating Account/Contact/Case "because we needed extra fields"** → custom fields and record types on the standard object.
- **Master-detail for convenience** → lookup unless the child truly dies with the parent or needs roll-ups.
- **Record types standing in for a separate object** → separate object when lifecycle or sharing differs. (Every object still carries at least one record type — see `salesforce-org-conventions`.)
- **Feature flags in a Custom Setting** → Custom Metadata Type; Custom Setting only for per-user/hierarchy or runtime-written values.
- **Public Read/Write OWD "to make it work"** → Private plus sharing rules; restriction rules to narrow.
- **End-user permissions on profiles** → permission set groups. (The org's standing exception is FLS on the four admin profiles, which every new field gets.)
- **One integration user owning millions of records** → distribute ownership, avoid skew.
- **Async SOQL for Big Object reporting** → Batch Apex or Bulk API 2.0 query.
- **Upserting on Name** → external Id.
- **"Let's copy the Data 360 DMOs into core objects"** → act in core, harmonise in Data 360.
## Go deeper
- `references/sharing-model.md` — the layer-by-layer sharing table, Apex sharing and recalculation, Experience Cloud licences, User Access Policies.
- `references/large-data-volumes.md` — selectivity and indexes, skew, skinny tables, Big Object archiving, migration loads.
- `references/erd-and-dictionary.md` — worked Mermaid ERD, data dictionary rows with classification, sharing matrix.
- Sibling skills: `salesforce-security` for enforcing access in code, `salesforce-integration` for external Ids and sync contracts, `apex-development` for roll-up and query code, `salesforce-automation` for automation on the model, `salesforce-code-review` for pull requests, `salesforce-org-conventions` for the org's naming and mandated patterns.