Two custom objects: Order__c and OrderLine__c. The line references the order. Should the relationship be master-detail or lookup? The decision is permanent for practical purposes. Migration from one to the other later requires data export, schema change, data re-import. On a million-row table, that is a multi-day project.
The right answer depends on five criteria. Most teams get one or two right and miss the others, ending up with a schema that constrains future work in ways that only emerge when adding a new requirement.
What each relationship type does
Master-detail: the child cannot exist without a parent. Deleting the parent cascades to the child. The child inherits the parent's sharing model and ownership. Rollup summary fields on the parent can aggregate child data.
Lookup: the child can exist without the parent (the lookup field is optional unless explicitly marked required). Deleting the parent does not delete the child by default (configurable to nullify or restrict). The child has its own sharing model and owner. No rollup summary fields directly (use Apex or third-party tools).
Hierarchical: a special variant only available on the User object. Allows User-to-User relationships (manager hierarchy).
Many-to-many via junction object: an intermediate object with two master-detail relationships. Models real many-to-many cases where no single parent makes sense.
The five decision criteria
For each new relationship, work through:
Criterion 1: Can the child exist without the parent?
If no (an order line is meaningless without an order), strong signal for master-detail.
If yes (a Contact can exist without a related Project), strong signal for lookup.
This is the most fundamental question and the one most teams get right.
Criterion 2: Should deleting the parent cascade to delete the children?
Master-detail: parent delete cascades. Children gone with no extra action.
Lookup with "Don't allow deletion of the lookup record that's part of a lookup relationship": parent cannot be deleted while children reference it.
Lookup with "Clear the value of this field": parent delete sets child lookup field to null. Children orphaned.
Lookup with default behavior: parent delete fails if children exist, otherwise allows.
The right behavior depends on the business. An OrderLine without an Order is garbage; cascade is right. A Contact without a Project should remain valid; nullification or restriction is right.
Criterion 3: Should the child inherit the parent's sharing model?
Master-detail: child sharing is the parent's. A user who can see the Order can see all OrderLines. A user who cannot, cannot.
Lookup: child has its own sharing. The user's access to the child is independent of the parent.
Most child-of-business-object relationships want master-detail sharing (order lines belong to whoever owns the order). Some specifically want lookup sharing (a Case linked to an Account, where Case and Account have different ownership models).
Criterion 4: Do you need rollup summary fields?
Master-detail supports native rollup summaries on the parent: count of children, sum of a child field, min, max, average over a child field.
Lookup does not. Achieving the same effect requires Apex or a third-party tool (DLRS, Rollup Helper).
If the parent needs to display "Total Amount" computed from child line items, master-detail is the cheapest path. Lookup forces custom development.
Criterion 5: How many master-detail relationships does the child have already?
Salesforce limits each custom object to 2 master-detail relationships. A junction object (the canonical many-to-many) uses both slots.
If the child already has 2 masters, the third relationship must be lookup. Plan accordingly.
Common scenarios mapped
Order to OrderLine. OrderLine cannot exist without Order. Order delete cascades. Order needs total computed from lines. Sharing inherits.
Recommendation: master-detail.
Account to Contact. Standard Salesforce uses lookup for this relationship by design. Contact can exist without an Account (orphan Contacts are legitimate). Sharing on Contact has its own model.
Recommendation: lookup (Salesforce's choice; users cannot change this for standard objects).
Project to ProjectTask, where ProjectTask also belongs to an Assignee (User).
ProjectTask is the child. Two parents: Project and Assignee. Project relationship: master-detail (task cannot exist without project, sharing should inherit). Assignee relationship: lookup (the user owns the task as an owner, not via sharing inheritance).
Recommendation: master-detail to Project, lookup to Assignee (also owner).
Course to Student via Enrollment.
Many-to-many: a course has many students, a student takes many courses. Enrollment is the junction. Enrollment has master-detail to both Course and Student. Sharing inherits from whichever parent's role is dominant (often the Student for educational orgs).
Recommendation: junction object Enrollment with two master-detail relationships.
Asset to ParentAsset (asset hierarchy).
The same object referencing itself. Salesforce supports hierarchical lookup on the User object specifically; for other objects, use a self-referencing lookup (not master-detail; cycles are not allowed).
Recommendation: lookup to itself (named "Parent Asset"). Apex enforces non-circular logic.
When to use a junction object
The canonical signal is many-to-many: each side has many of the other side. Examples:
- Users have many Skills; Skills are held by many Users.
- Products have many Markets; Markets contain many Products.
- Articles have many Topics; Topics tag many Articles.
The junction is named for the relationship: User_Skill__c, Product_Market__c, Article_Topic__c. The junction has two master-detail relationships (one to each side) and any per-relationship attributes (e.g., proficiency level on User-Skill).
Anti-pattern: modeling many-to-many as a multi-select picklist on one side. The picklist becomes a maintenance burden, breaks reporting, and cannot store relationship-specific attributes.
Common relationship mistakes
Five patterns Sapota has seen in audits:
1. Lookup when master-detail was correct.
Order to OrderLine via lookup. OrderLine can be created without an Order. Garbage data accumulates. Total Order Amount has to be computed in Apex.
Fix: convert to master-detail. The migration requires backfilling parent IDs (no orphans allowed) and may take a maintenance window. Done early, the conversion is cheap. Done late, it is a project.
2. Master-detail when lookup was correct.
Contact to Account as master-detail (in a custom object scenario). Now Contact sharing inherits from Account, which is not the intent. Contacts cannot be reassigned across Accounts cleanly.
Fix: convert to lookup. Migration is straightforward but the loss of cascading delete and rollups requires Apex backfill.
3. Lookup for many-to-many.
A multi-select picklist of regions on Product. Reporting is awful, integration is awful, removing a region from the master list breaks records.
Fix: create a Region junction object. Backfill from the picklist values. Update integrations.
4. Too many master-detail relationships.
Child object that wants three master-detail parents. Hits the 2-master limit. Has to use lookup for the third.
Fix: rethink the model. Maybe one of the "parents" is actually a sibling or a different kind of relationship. Maybe two of them should be combined into a single parent with a type field.
5. Hierarchical for non-user data.
A developer tries to use "hierarchical" relationship type for a custom Asset hierarchy. Hierarchical is User-only.
Fix: use a self-referencing lookup. Add Apex validation to prevent cycles.
Apex implications
Code that touches related objects depends on the relationship type:
- Master-detail relationships expose the parent fields via dot-walk in SOQL (
SELECT Order__r.Account__r.Name FROM OrderLine__c). Lookup relationships also support dot-walk but with different sharing semantics. - Deleting a master cascades; deleting a child via Apex requires explicit DML on the child. Code that deletes the master should not also explicitly delete the children (double-delete error).
- DML on a master-detail child requires the user to have edit access on the master. Apex without sharing bypass means user permission matters.
Test data factories should reflect the relationship types. Master-detail children cannot be inserted without a parent. Factory methods need to create the parent first.
What good schema design looks like
A Salesforce org with healthy relationship modeling:
- Every custom relationship has a documented rationale for master-detail vs lookup.
- Junction objects used for genuine many-to-many; multi-select picklists avoided.
- Self-referencing lookups for hierarchies on custom objects, with cycle prevention.
- ERD documentation kept current and used in onboarding.
- Schema changes reviewed against the five criteria before deployment.
Sapota's Salesforce team holds the Platform Developer I credential and reviews schema design as part of every implementation. The cost of getting a relationship type wrong is paid in the migration project years later, when the original decision-maker is gone and the data volume makes the fix expensive.
Designing or refactoring Salesforce schema and relationships? Sapota's Salesforce team handles data model design, junction object setup, and relationship migration on production engagements. Get in touch ->
See our full platform services for the stack we cover.
