18. Data Modeling Practice

Step 4 of every design ("APIs & data model") is where many candidates are weakest. They name entities but can't show tables, keys, indexes, or how the model serves the queries. This section is practice: one domain modeled three ways, then the patterns and anti-patterns interviewers probe.

18.1 The method: access patterns first

 1. List entities and relationships (nouns + cardinalities: 1:1, 1:N, M:N)
 2. List ACCESS PATTERNS with frequency & latency needs  ← drives everything below
 3. Choose the store per access-pattern group (§6.2)
 4. Relational: normalize (3NF), then denormalize deliberately for hot reads
    NoSQL: design keys so each hot access pattern is ONE key lookup or ONE range scan
 5. Add indexes for each remaining access pattern; count the write cost of each
 6. Check invariants: uniqueness, foreign keys, money, time, concurrency (who can write a row at once?)
 7. Check evolution: how will this change in 2 years? Which key is a one-way door?

Running example: an e-commerce order domain.

18.2 Exercise 1: relational model (Postgres)

CREATE EXTENSION IF NOT EXISTS citext;               -- case-insensitive text type

CREATE TABLE customers (
  customer_id  BIGINT PRIMARY KEY,             -- Snowflake/UUIDv7-style, time-ordered
  email        CITEXT NOT NULL UNIQUE,          -- case-insensitive uniqueness
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE categories (
  category_id  BIGINT PRIMARY KEY,
  name         TEXT NOT NULL,
  parent_id    BIGINT REFERENCES categories(category_id)   -- adjacency-list hierarchy (§18.5)
);

CREATE TABLE products (
  product_id   BIGINT PRIMARY KEY,
  sku          TEXT NOT NULL UNIQUE,
  name         TEXT NOT NULL,
  category_id  BIGINT NOT NULL REFERENCES categories(category_id),
  price_minor  BIGINT NOT NULL CHECK (price_minor >= 0),   -- integer minor units (cents)
  currency     CHAR(3) NOT NULL
);

CREATE TABLE inventory (
  product_id   BIGINT PRIMARY KEY REFERENCES products(product_id),
  available    INT NOT NULL CHECK (available >= 0),         -- invariant enforced by the DB
  version      BIGINT NOT NULL DEFAULT 0
);

CREATE TABLE orders (
  order_id     BIGINT PRIMARY KEY,
  customer_id  BIGINT NOT NULL REFERENCES customers(customer_id),
  status       TEXT NOT NULL CHECK (status IN ('PENDING','PAID','SHIPPED','CANCELLED')),
  total_minor  BIGINT NOT NULL,
  currency     CHAR(3) NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  version      BIGINT NOT NULL DEFAULT 0                     -- optimistic concurrency
);

CREATE TABLE order_items (
  order_id         BIGINT NOT NULL REFERENCES orders(order_id),
  line_no          INT NOT NULL,
  product_id       BIGINT NOT NULL REFERENCES products(product_id),
  quantity         INT NOT NULL CHECK (quantity > 0),
  unit_price_minor BIGINT NOT NULL,      -- SNAPSHOT of price at purchase: deliberate denormalization
  product_name     TEXT NOT NULL,        -- snapshot too: receipts must not change if the catalog does
  PRIMARY KEY (order_id, line_no)
);

-- AP2: a customer's orders, newest first (composite index matches the ORDER BY exactly)
CREATE INDEX orders_by_customer ON orders (customer_id, created_at DESC, order_id DESC);
-- AP3: fulfillment queue — PARTIAL index: only the small "active" subset is indexed
CREATE INDEX orders_paid_queue ON orders (created_at) WHERE status = 'PAID';

How each access pattern is served:

-- AP1: one PK lookup + one PK range scan (order_items PK starts with order_id)
SELECT * FROM orders WHERE order_id = $1;
SELECT * FROM order_items WHERE order_id = $1 ORDER BY line_no;

-- AP2: keyset (cursor) pagination with a tiebreaker (§5.7)
SELECT order_id, status, total_minor, created_at FROM orders
WHERE customer_id = $1 AND (created_at, order_id) < ($2, $3)
ORDER BY created_at DESC, order_id DESC LIMIT 20;

-- AP3: workers claim paid orders without blocking each other
SELECT order_id FROM orders WHERE status = 'PAID'
ORDER BY created_at LIMIT 50 FOR UPDATE SKIP LOCKED;

-- AP5: atomic, oversell-proof decrement (no read-modify-write race)
UPDATE inventory SET available = available - $2, version = version + 1
WHERE product_id = $1 AND available >= $2;          -- 0 rows updated → out of stock

-- Optimistic concurrency for order updates (lost-update protection, §6.4)
UPDATE orders SET status = 'CANCELLED', version = version + 1, updated_at = now()
WHERE order_id = $1 AND version = $2;               -- 0 rows → someone else changed it; reload

AP6 (reporting) stays off this database. CDC streams it into the warehouse (§21).

Interview talking points:

• Price and name in order_items are intentionally denormalized. They are historical facts, not references. Saying why is the signal.
• The CHECK (available >= 0) constraint is a last line of defense even if application logic has a bug.
• Partial indexes keep the fulfillment-queue index tiny even with a billion historical orders.
• Scale path: shard by customer_id (AP2 stays single-shard). AP1 by order_id then needs routing, so embed the shard in the order ID (§9.7, Instagram-style), and AP3 becomes a per-shard queue or moves to a dedicated queue.

18.3 Exercise 2: the same domain in DynamoDB (single-table design)

In key-value / document stores you design keys for access patterns, and often put several entity types in one table so related items share a partition ("item collections").

AP1 Get order + items:   Query PK = "ORDER#9001"                      → header + all items, one request
AP2 Customer's orders:   Query PK = "CUST#123", SK begins_with "ORDER#", ScanIndexForward=false, Limit 20
AP3 Orders by status:    GSI1: GSI1PK = "STATUS#PAID#" + shard(0..9), GSI1SK = created_at
                         (sharded "write-sharded GSI" to avoid one hot partition for all PAID orders;
                          fulfillment queries the 10 shards and merges)
AP4 Product:             GetItem PK = "PROD#55", SK = "DETAILS"
AP5 Inventory:           UpdateItem PROD#55/STOCK
                         SET available = available - :q  CONDITION available >= :q
Create customer with unique email: TransactWriteItems [
   Put CUST#123/PROFILE,
   Put EMAIL#a@x.com/UNIQUE  CONDITION attribute_not_exists(PK) ]   ← uniqueness via a guard item

Trade-offs to state: single-table design gives excellent performance for known access patterns and is painful for new ones (often a new GSI plus a backfill). Ad-hoc queries go to an analytics export. The order is stored twice (under the customer and under its own ID), so those writes must be transactional, or one copy derived from the other via streams.

18.4 Exercise 3: wide-column (Cassandra/ScyllaDB): one table per query

Wide-column modeling is query-first: you create a table for each read pattern and write to several tables (denormalization is the norm).

-- AP2: orders by customer, newest first
CREATE TABLE orders_by_customer (
  customer_id bigint,
  created_at  timestamp,
  order_id    bigint,
  status      text,
  total_minor bigint,
  PRIMARY KEY ((customer_id), created_at, order_id)
) WITH CLUSTERING ORDER BY (created_at DESC, order_id DESC);

-- Time-series style data needs BUCKETING so partitions stay bounded (§38 corner cases):
CREATE TABLE order_events (
  order_id   bigint,
  day        date,          -- bucket
  event_time timeuuid,
  type       text,
  payload    text,
  PRIMARY KEY ((order_id, day), event_time)
);

No joins and no ad-hoc filters. Every query must provide the full partition key. Writes go to several tables, so use logged batches for atomicity within the same partition, or accept that tables converge independently and repair them.

18.5 Modeling patterns interviewers probe

18.6 Anti-patterns (and what to say instead)

18.7 Practice drills (do each in 10 minutes, then compare)