nosql-database-design

NoSQL Database Design Skill

Step 1 — Access Pattern Analysis

List every query the application must answer before touching the schema. NoSQL schemas are derived from access patterns, not from entity relationships.

Access patterns for an e-commerce system:
1. Get order by order_id
2. List all orders for a user (paginated, newest first)
3. List all orders with status=PENDING (for fulfillment worker)
4. Get all line items for an order
5. Get product by product_id
6. List products by category

Identify for each pattern:

• Query type: GetItem (by exact key) or Query (by key + filter)
• Cardinality: how many results (1, tens, millions?)
• Sort order: unsorted, by date, by score?
• Filter: additional attributes to filter on after key lookup?

Step 2 — Choose the Model

Factor	DynamoDB	MongoDB
Access patterns	Known and stable	Flexible / evolving
Scale	10 B+ items, unlimited throughput	Moderate to high scale
Joins	Not supported — model for single-table	$lookup supported (avoid in hot paths)
Transactions	Supported (2-phase, up to 100 items)	Multi-document transactions supported
Consistency	Eventually consistent reads by default (strongly consistent optional)	Tunable read/write concern

Step 3 — DynamoDB: Single-Table Design

Use a single table with a generic PK and SK:

Table: ecommerce
PK (partition key)     SK (sort key)            Attributes
─────────────────────  ─────────────────────────  ──────────────────────────
USER#u-123             PROFILE                    name, email, created_at
USER#u-123             ORDER#o-456               status, total, placed_at
USER#u-123             ORDER#o-789               status, total, placed_at
ORDER#o-456            ITEM#p-001                quantity, unit_price
ORDER#o-456            ITEM#p-002                quantity, unit_price
PRODUCT#p-001          METADATA                  name, price, category

GSI (Global Secondary Index) for access patterns that don't fit the base key: