Review a Relational Schema and Redesign It for Document, Wide-Column, and Key-Value Workloads

Learning outcomes

A useful technology comparison keeps the business facts constant. This lesson begins with a normalized relational source and redesigns only the physical representations needed for three different workloads. The result is not “replace SQL with NoSQL”; it is a record of which access contract, atomicity boundary, duplication, size bound, and operational responsibility each model accepts.

1. Baseline: normalized authority makes relationships explicit

The baseline schema contains customers, products, orders, and order_items with primary keys, foreign keys, and a positive-quantity check. This shape supports ad-hoc joins and centralized constraints well. Physical redesign should preserve the meaning of those facts while optimizing specific access patterns. Do not compare database families by loading completely different datasets or silently weakening invariants.

2. Three physical redesigns for three different jobs

The same application can use all three while retaining relational authority for invariants that benefit from it. Polyglot persistence only works when ownership and synchronization boundaries are explicit.

3. AtlasMart lab: materialize all three from one relational source

This lab uses Python’s built-in sqlite3 module to create the normalized source, then materializes three workload-specific representations in memory. It makes the baseline constraints and the reshaped access paths visible without installing any external product.

import sqlite3
from collections import defaultdict

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE customers(id TEXT PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE products(id TEXT PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL);
CREATE TABLE orders(id TEXT PRIMARY KEY, customer_id TEXT NOT NULL REFERENCES customers(id), created_at TEXT NOT NULL, status TEXT NOT NULL);
CREATE TABLE order_items(order_id TEXT NOT NULL REFERENCES orders(id), product_id TEXT NOT NULL REFERENCES products(id), qty INTEGER NOT NULL CHECK(qty>0), unit_price REAL NOT NULL, PRIMARY KEY(order_id, product_id));
""")
con.executemany("INSERT INTO customers VALUES(?,?)", [("c1","Ava"),("c2","Noah")])
con.executemany("INSERT INTO products VALUES(?,?,?)", [("p1","Trail Runner",120),("p2","Bottle",20),("p3","Headphones",220)])
con.executemany("INSERT INTO orders VALUES(?,?,?,?)", [
    ("o1","c1","2026-08-01T10:00:00","paid"),
    ("o2","c1","2026-08-22T09:00:00","shipped"),
    ("o3","c2","2026-08-24T12:00:00","placed"),
])
con.executemany("INSERT INTO order_items VALUES(?,?,?,?)", [
    ("o1","p1",1,120),("o1","p2",2,20),("o2","p3",1,220),("o3","p2",3,20)
])

# 1) Document redesign: order is the aggregate; immutable line snapshots are embedded.
documents = []
for oid,cid,created,status in con.execute("SELECT id,customer_id,created_at,status FROM orders"):
    lines = [{"product_id":pid,"qty":qty,"unit_price":price} for pid,qty,price in con.execute("SELECT product_id,qty,unit_price FROM order_items WHERE order_id=?",(oid,))]
    documents.append({"order_id":oid,"customer_id":cid,"created_at":created,"status":status,"lines":lines})

# 2) Wide-column redesign: one query-shaped table for recent orders by customer+month.
wide = defaultdict(list)
for d in documents:
    month = d["created_at"][:7]
    wide[(d["customer_id"], month)].append((d["created_at"], d["order_id"], d["status"]))
for k in wide: wide[k].sort(reverse=True)

# 3) Key-value redesign: ephemeral sessions + idempotency records; not the order source of truth.
kv = {
    "session:t1:u:c1": {"cart_count":2,"ttl_s":2700},
    "idem:t1:checkout:req-900": {"order_id":"o1","ttl_s":86400},
}

print("relational joined rows for o1:", con.execute("SELECT COUNT(*) FROM order_items WHERE order_id='o1'").fetchone()[0])
print("document o1 line count:", next(d for d in documents if d["order_id"]=="o1")["lines"] and len(next(d for d in documents if d["order_id"]=="o1")["lines"]))
print("wide partitions:", sorted(wide.keys()))
print("c1 August order ids:", [x[1] for x in wide[("c1","2026-08")]])
print("kv keys:", sorted(kv))
print("document gives up: ad-hoc cross-order joins/FKs inside one aggregate representation")
print("wide-column gives up: arbitrary query flexibility; tables are query-shaped and duplicated")
print("key-value gives up: relational joins and rich predicates; keys must encode the access contract")
print("authority remains explicit: orders are durable business records; session/idempotency state has separate retention")

4. Document redesign: aggregate locality with a hard growth rule

Embed order lines because they are owned by the order and normally read with it. Copy the unit price and product label as historical snapshots, not as mutable catalog mirrors. Bound line count and total document size; unusually large orders may require child documents or another strategy. MongoDB 8.3 is a current implementation example where embedding provides single-record locality but documents are capped at 16 MiB. That product limit is not the definition of document modeling; the general rule is to prove a safe bound for the chosen implementation.

5. Wide-column redesign: query-first partition locality

For “recent orders by customer,” choose a composite partition key such as (tenant_id, customer_id, month) and cluster by created_at DESC. This avoids scanning unrelated customers and bounds growth by month. The tradeoff is duplication: status changes from Orders must update or project into this table, and a new query such as “orders by product across all customers” probably needs another access path. Cassandra 5.0.9 is a current implementation example of this partition/clustering model.

6. Key-value redesign: exact-key ephemeral state

Sessions and idempotency records fit an exact-key contract: session:t1:u:c1 or idem:t1:checkout:req-900. TTL is part of the state lifecycle, not a durability guarantee. If an idempotency record protects payment retries, its retention window must cover the maximum retry/reconciliation interval. Redis 8.10 is a current implementation example of rich key/value and data-structure operations, but the chapter’s mandatory design does not depend on Redis semantics.

7. Wrong universal redesign and migration risk

A common failure is to move the entire normalized schema into one document collection, one wide-column table, or one key-value namespace because the chosen technology is fashionable. That preserves neither query efficiency nor relational constraints automatically. Migrate one access path at a time: shadow-write or project, backfill, compare results, measure freshness/latency, cut read traffic gradually, retain rollback, and keep the old authority until the new invariant and recovery behavior are proven.

8. Production judgment and bridge to transactions

Document the maximum object/partition size, keys, indexes, atomicity scope, source of truth, consistency model, derived-copy freshness, tenant isolation, backup/rebuild path, failure injection, cost, and operator skill for every model. The redesign intentionally exposes where one-record atomicity is valuable and where cross-record invariants remain. Chapter 16 starts exactly there: transactions beyond one record, optimistic conditions, two-phase commit, sagas, and compensation.

Wrong approach: pick one database family and translate every relational table mechanically

A mechanical translation carries the old shape without gaining the new model’s locality while often losing joins, constraints, or transaction scope. The repair is workload-by-workload redesign with explicit authority, bounds, synchronization, and a record of capabilities intentionally given up.

Verification, cleanup, and production checklist

Verification is the deterministic program output plus the reasoning checks below. Cleanup is simply deleting the local lesson5.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.

• SQLite — Foreign Key Support — Relational baseline for explicit referential integrity in the local lab.
• MongoDB — Embedded Data in Your Schema — Current document-model example; includes the 16 MiB document size limit.
• Apache Cassandra — CQL data definition — Current partition-key/clustering-column example and query-driven locality guidance.
• Redis Open Source 8.10 release notes — Current optional key-value/data-structure implementation snapshot: Redis 8.10.0 GA, July 2026.
• PostgreSQL 18 — Constraints — Current relational reference for primary, foreign-key, unique, and check constraints.