Start from Queries, Commands, Invariants, Cardinalities, and Change Rates

Learning outcomes

Build an access-pattern worksheet before choosing a document shape, partition key, index, or cache key. Then prove why a normalized-looking or aesthetically simple schema can still be operationally wrong when common requests scatter across partitions or read without a bound.

1. Physical design starts with verbs, not nouns

A relational ER diagram starts from entities and relationships because normalization is a powerful general-purpose way to preserve facts without uncontrolled duplication. Distributed physical design adds another question: what exact work must each command or query perform at runtime? A command asks the system to change state; a query asks it to return state without changing the business fact. An invariant is a condition that must remain true across successful changes. Cardinality describes how many matching objects or relationships may exist. A change rate describes how often a fact mutates. These attributes determine whether locality, duplication, synchronization, or a transaction boundary is valuable.

2. Build the access-pattern worksheet before the schema

The worksheet is not documentation theater. It is a falsifiable design input. For each row, ask: can the proposed primary/partition key route the request directly? Is the maximum result set bounded? Does sorting require work across partitions? Does the invariant fit one atomic boundary? If not, the model needs another access path or a different ownership boundary.

3. AtlasMart lab: reject scatter-gather with evidence

Save the following as lesson1_access_patterns.py and run it with python lesson1_access_patterns.py. It creates 2,400 synthetic orders across eight shards. The “bad” model routes by order identifier only, so a common customer-history query must inspect every shard. The query-shaped projection groups bounded customer-month partitions.

from collections import Counter, defaultdict

SHARDS = 8
orders = []
for customer in range(1, 41):
    for month in range(1, 13):
        for seq in range(5):
            order_id = f"o-{customer:02d}-{month:02d}-{seq}"
            orders.append({
                "order_id": order_id,
                "customer_id": f"c-{customer:02d}",
                "month": month,
                "status": "paid" if seq % 2 == 0 else "shipped",
                "amount": 40 + customer + seq,
            })

def shard_for_order_id(order_id):
    return sum(order_id.encode()) % SHARDS

# Elegant-looking but workload-hostile: authority keyed only by order_id.
by_order = defaultdict(list)
for o in orders:
    by_order[shard_for_order_id(o["order_id"])].append(o)

customer = "c-17"
months = {11, 12}
scanned_bad = 0
found_bad = []
for shard_rows in by_order.values():
    scanned_bad += len(shard_rows)
    found_bad += [o for o in shard_rows if o["customer_id"] == customer and o["month"] in months]

# Query-shaped projection: bounded customer-month partitions.
by_customer_month = defaultdict(list)
for o in orders:
    by_customer_month[(o["customer_id"], o["month"])].append(o)

scanned_good = 0
found_good = []
for m in months:
    rows = by_customer_month[(customer, m)]
    scanned_good += len(rows)
    found_good += rows

print("orders total:", len(orders))
print("bad model shards touched:", len(by_order), "of", SHARDS)
print("bad model rows examined:", scanned_bad)
print("good model partitions touched:", len(months))
print("good model rows examined:", scanned_good)
print("same logical result:", sorted(o["order_id"] for o in found_bad) == sorted(o["order_id"] for o in found_good))

worksheet = [
    ("GetOrder", "order_id", "1", "p95<=80ms", "exact order", "rare change"),
    ("ListCustomerOrders", "customer_id+month", "<=100/page", "p95<=120ms", "no omission", "append-heavy"),
    ("PlaceOrder", "order_id", "1 write", "p95<=180ms", "total >= 0", "450/s peak"),
]
print("worksheet rows:", len(worksheet))
print("rejected reason: scatter-gather + unbounded scan for common customer-history query")

4. Elegant logical schemas can still be operationally wrong

A schema may be perfectly normalized and still create a poor distributed access path. If ListCustomerOrders is among AtlasMart’s dominant reads, an order table partitioned only by a globally distributed order_id may force scatter-gather across all partitions unless a secondary projection exists. Similarly, storing all customer history under one customer key may solve routing but create an unbounded partition that grows for years. Good physical design therefore balances locality with boundedness.

The deliberately wrong response is to “fix” every query by adding a new duplicate table immediately. That can replace read amplification with uncontrolled write fan-out. The worksheet must also record mutation rate, synchronization tolerance, failure recovery, index/storage cost, and which fact is authoritative.

5. Invariants decide where flexibility stops

Not all AtlasMart data can accept the same consistency tradeoff. A search result may lag a catalog update for several seconds if freshness is visible and repairable. An order total must not silently diverge from accepted line items. Inventory that must never go negative may need single-owner serialization, conditional mutation, reservation, or a transactional workflow rather than a weakly synchronized duplicate. Model the strongest invariant first; then choose the weakest consistency and smallest coordination scope that still preserves it.

6. Production judgment and bridge

Before approving a physical model, review routing fan-out, maximum partition/object size, hot-key risk, write amplification, tail-latency sensitivity, durability, restore/rebuild path, tenant isolation, authorization on every derived path, observability, migration/rollback, and operational skill. A simulator can reveal hidden work, but real acceptance requires production-like data distributions and failure tests. The next lesson treats denormalization explicitly as precomputed work and makes its write/repair bill visible.

Wrong approach: design from entities alone and “let the database figure out the queries”

The failure is not that normalized models are bad. It is that a distributed physical design that ignores dominant query keys, cardinality, and bounds can turn an ordinary request into scatter-gather or an unbounded scan. Repair the design by recording the workload, preserving authority, and adding only the access paths whose benefit justifies their write and repair cost.

Verification, cleanup, and production checklist

Verification is the deterministic program output plus the reasoning checks below. Cleanup is simply deleting the local lesson1.py file because the lab creates no external service or persistent database. In production, repeat the design with representative cardinality/skew, tail-latency, failure injection, tenant-isolation tests, restore/rebuild drills, migration rollback, and cost/capacity evidence before committing to a physical model.

References

Foundational statements are kept vendor-neutral. Version-sensitive examples use current official documentation and are labeled as examples rather than definitions.

• Apache Cassandra — CQL data definition — Current query-driven partition-key/clustering-column example and locality tradeoffs.
• MongoDB — Embedded Data in Your Schema — Current workload-driven example of embedding related data for locality and atomicity.
• MongoDB — Reference Data in Your Schema — Current example of choosing references when duplication or relationship shape makes embedding unattractive.
• PostgreSQL 18 — Indexes — Relational baseline showing that alternate access paths improve reads while adding maintenance overhead.