Data modelling in depth: the schema you will still like in year three

Record types share a schema and its automation. A second object shares none of it. Both are correct answers to different questions.

1. VideoRecord types share a schema, not a decisionRecord types divide the picklist values and the page layout. They do not divide the automation, the sharing model or the reporting.Preview6 min
2. VideoOne object, many life cycles: when record types earn their keep7 min
3. ReadingTwo objects, and the point where they diverge6 min
4. VideoPage layouts forking on record type, and the maintenance cost nobody counts7 min
5. VideoAutomation branching on record type, and the day it branches twice6 min
6. LabLab: model one intake process as record types, then as two objects6 min

A many-to-many relationship reports correctly only when the junction is modelled as its own object with its own fields.

1. VideoThe many-to-many nobody modelled, and the duplicate it produced7 min
2. VideoA junction object with its own fields, not just two lookups6 min
3. ReadingRoll-up counting through a junction, and where it doubles7 min
4. VideoSharing through a junction: whose access wins6 min
5. VideoDeleting one side of a junction, and what happens to the other7 min
6. LabLab: build a junction object and prove the count is right6 min

A relationship is one decision that pays or costs in four different places: rollups, sharing, reporting and deletion. Name all four before you build it.

1. VideoRollups: the first place a lookup costs you6 min
2. VideoSharing: what a lookup does not inherit7 min
3. VideoReporting: the join a report type has to declare6 min
4. VideoDeletion: cascade, restrict, and the lookup that quietly orphans7 min
5. ReadingThe four places, side by side, on one relationship6 min
6. LabLab: trace one relationship through all four places7 min
7. VideoRedesigning a relationship after you have seen all four costs6 min

An external id is the difference between a re-run that updates and a re-run that duplicates. Choose it before the first record loads.

1. VideoChoosing an external id before the first record loads6 min
2. VideoUpsert on an external id, and the re-run that does not duplicate7 min
3. ReadingExternal ids across objects: keeping one source of truth6 min
4. VideoThe external id that was not unique, and what it cost7 min
5. VideoIdempotent loads at scale: the pattern that survives a retry6 min
6. LabLab: load the same dataset twice and prove nothing duplicated7 min

A field, a custom setting, custom metadata, or a row. Five hard-coded rates in one engine turned out to be a schema decision, not a formula bug.

1. VideoA field, a custom setting, custom metadata, or a row: four places, one decision6 min
2. VideoCustom metadata as a rate table: the tax engine caseFive of eight hard-coded rates in a real tax engine turned out to sit inside formula fields, which can reference custom metadata but not a custom object. That fact decided the whole rebuild.6 min
3. ReadingWhy a formula field can reference custom metadata and not a custom object7 min
4. VideoCustom settings: what they are still for6 min
5. VideoA province-by-tax-class table: 28 records instead of 28 formulas7 min
6. LabLab: move a hard-coded rate into custom metadata6 min
7. VideoThe value that should have been a row, not a picklist7 min

A lifecycle modelled clearly the first time never grows a second picklist wearing a number. This module is how to see the stages before you build the field.

1. VideoReading a lifecycle before you model it6 min
2. VideoThe picklist that grew a second version, and why6 min
3. ReadingStatus, stage and sub-status: picking one vocabulary7 min
4. VideoA state machine as a picklist, guarded by validation rules6 min
5. LabLab: model a five-stage order lifecycle end to end7 min
6. VideoMigrating a lifecycle without breaking every report built on the old one6 min

One organising question runs the whole design: where is the inventory value now? A real org answered it with two ledgers, and documented both.

1. VideoThe design axiom: where is the inventory value now?Every inventory screen, state and rollup in this case study answers the same question. Naming it first is what kept the schema coherent.7 min
2. VideoA state machine for stock: placed, warehouse, in transit, on site, consumed, invoiced6 min
3. ReadingTwo parallel stock ledgers, both real, both audited, and the documentation says soA migration and a later system install left the org carrying two stock ledgers. Both are real. The schema says so instead of pretending otherwise.6 min
4. VideoThe transfer is the costing event: a rule captured verbatim7 min
5. LabLab: reconcile two ledgers and document which one is load bearing6 min
6. VideoThe rule that stops a schema from ever inventing a third ledger7 min

A schema with no written intent is a schema someone else will guess at. Documentation is the last design decision, not an afterthought.

1. VideoField descriptions and help text, written for the next admin6 min
2. VideoA data dictionary that explains intent, not just field names7 min
3. ReadingThe schema decision log: what to write down and where6 min
4. LabLab: document an existing custom object properlyThe capstone lab for the course. Take an unfamiliar object graph and produce the decision log a new admin would need on day one.38 min
5. VideoHanding a schema to someone who will extend it without you6 min
6. QuizCheck: data modelling in depth14 min