Data Modeling
Begin with questions the product must answer, not a choice between SQL and NoSQL.
Begin with questions the product must answer, not a choice between SQL and NoSQL. “Show delivery attempts for this notice” and “find recipients who opted out” imply different keys and lifecycles. A model is useful when it makes ownership and invariants explicit. It should also distinguish business facts from convenient projections that can be rebuilt.
Learning goals
Access patterns; entities; keys; ownership; one-to-many and many-to-many relationships; normalization; derived views; uniqueness; transactions; evolution.
The mechanism at a glance
Figure — Notice → Delivery (1 to many); Delivery → Attempt (1 to many); Recipient → Delivery (recipient); Recipient → Preference (live consent); Delivery → Dashboard view (derived counts)
The numbered components identify responsibilities. Follow the labeled arrows rather than treating the numbers as a global execution order. The scenario later in this lesson shows one concrete sequence.
Step-by-step reasoning
1. Name entities and lifetimes
A notice is an intent, a recipient is an identity, a delivery is one intended recipient/channel pair, and an attempt is one execution of that delivery. Combining all four into one mutable row makes retries and history difficult to explain. Give each stable identity and state who may change it. Decide which records are retained, anonymized, or deleted under the product’s retention policy.
2. Write the access patterns
List lookups before indexes: notice by ID within tenant, recent deliveries by notice, attempts by delivery, and current channel preference by recipient. Represent many-to-many membership with an explicit relation when membership has its own attributes. Avoid storing a growing unbounded array of recipients inside a notice document; pagination and concurrent updates become awkward.
3. Enforce invariants at the owner
If there may be only one logical delivery per notice, recipient, and channel, use a unique constraint or an equivalent conditional write at the authoritative store. Checking first in application code races. A transaction can commit notice metadata and its outbox event together. Keep external provider calls outside that transaction so network latency does not hold locks open.
4. Separate snapshots from live decisions
The audience selected for a campaign may be a historical snapshot, while consent must be checked again near dispatch. A snapshot answers who was selected at scheduling time; a live preference answers whether sending is currently allowed. Name both timestamps. Derived counts and search indexes may lag and can be rebuilt from authoritative records, but they must not authorize a send.
Contracts and state
The following sketch makes the decision boundary concrete. Field names and capacity assumptions are illustrative; adapt them to the stated product contract.
Notice(id, tenant_id, template_version, created_at)
Delivery(id, notice_id, recipient_id, channel, state)
UNIQUE(notice_id, recipient_id, channel)
Attempt(id, delivery_id, attempt_no, provider_ref, state)
Preference(tenant_id, recipient_id, channel, allowed, version)
Outbox(id, notice_id, event_type, published_at)Worked example
Imagine a notice for 10,000 recipients with three failed attempts for one recipient. A delivery row expresses the one intended outcome; attempt rows preserve each provider interaction. The delivery can eventually be marked delivered without deleting the failure history. A dashboard count is a projection over those delivery states, not the authority for deciding whether to send. When the dashboard projection is lost, replay state changes or recompute from deliveries.
Failure walkthrough
A schema migration adds a new required field while older writers still run. A hard cutover can reject valid writes or leave unreadable rows. Prefer an expand-and-contract sequence: add a compatible field, teach readers to tolerate its absence, backfill, update writers, verify completeness, then enforce the stronger constraint. Backfills should be bounded and restartable so they do not overwhelm production traffic.
Figure — Audience snapshot created → Recipient opts out → Delivery still queued → Dispatch reads live preference → Suppress and record reason
Decisions and trade-offs
| Representation | Advantage | Trade-off |
|---|---|---|
| Normalized records | Clear ownership and constraints | Joins or multiple lookups |
| Materialized view | Fast product-specific reads | Lag and repair work |
| Embedded bounded value | Simple atomic local updates | Poor fit for unbounded collections |
Check your understanding
A delivery worker sees no prior attempt and inserts a new one. Two workers do this at once. Where should uniqueness be enforced?
Show answer and explanation
Answer: At the authoritative write boundary with an appropriate unique key or conditional transition. A prior read cannot serialize the two workers. Preserve separate logical delivery and attempt identities so a legitimate retry is not confused with a second business intent.
Transfer to a new scenario
Model notices, recipients, preferences, attempts, and provider receipts; distinguish snapshots from live preferences.
Which invariant belongs in a database constraint rather than a prior application read?
Continue the connection
Study Database Indexing and explain which guarantee from this lesson carries into that topic.
Start from queries and invariants
Model a school notification inbox. The product needs recent deliveries for one recipient, status totals for one campaign and retryable attempts for one delivery. One document per campaign containing every recipient quickly becomes a hot, unbounded object. Separate campaign intent, recipient-channel delivery and provider attempt because they have different identities and lifecycles.
Normalize the authoritative facts you need to update consistently. Denormalize a read projection only after naming its source and rebuild strategy. A displayed campaign count can be eventually consistent while a unique delivery key prevents duplicate expansion. Store event time and ingestion time separately when late data matters.
Choose partition keys from access patterns. A tenant key helps isolation but one large tenant may outgrow a partition. A composite tenant-plus-bucket key distributes work at the cost of query fanout. A UUID gives identity, not an efficient query plan. Every important query needs an explicit index or bounded scan.
Figure — A decision worksheet for Data Modeling: read the mechanism and its guarantee together.
Operational sketch
campaign(id PK, tenant_id, template_version)
delivery(id PK,campaign_id,recipient_id,channel,state)
UNIQUE(campaign_id,recipient_id,channel)
attempt(id PK,delivery_id,provider_ref,state)
INDEX delivery(recipient_id,created_at,id)A tempting mistake
Duplicating data without an update and deletion protocol creates conflicting truths. Storing only the latest status also destroys the evidence needed to distinguish retries, late callbacks and manual corrections.
Transfer exercise
Where should a provider timeout be recorded if the delivery has two attempts?
Show answer and explanation
Answer: On the specific attempt, with delivery-level state derived under a transition policy. Overwriting the delivery as failed can erase a successful concurrent or late result.
A delivery worker sees no prior attempt and inserts a new one. Two workers do this at once. Where should uniqueness be enforced?
Your design draft
Clarify assumptions, explain your approach, and test the difficult cases. Save your draft, then compare it with the study notes.
Self-review checklist
Self-guided practice. Automated AI feedback and code execution are not connected.