Primary Key & Single-Table Design

Access Pattern Analysis:
─────────────────────────────────────────────────────────────
Pattern                          | Input        | Frequency
─────────────────────────────────────────────────────────────
Get user profile                 | userId       | High
Get all orders for a user        | userId       | High
Get specific order               | orderId      | Medium
Get orders by date range         | userId+dates | Medium
Get all items in an order        | orderId      | Medium
Get user's recent 10 orders      | userId       | High
Find orders by status (pending)  | status       | Low (admin)
─────────────────────────────────────────────────────────────

Key Design Decision:
  PK = USER#<userId>
  SK = USER#<userId>         → user profile
  SK = ORDER#<timestamp>#<orderId> → orders (sorted by time)
  
  GSI1: PK = ORDER#<orderId>, SK = ITEM#<itemId> → order items

Sort Key: COUNTRY#US#STATE#CA#CITY#SF

Query all US:     SK begins_with "COUNTRY#US"
Query California: SK begins_with "COUNTRY#US#STATE#CA"
Query SF:         SK begins_with "COUNTRY#US#STATE#CA#CITY#SF"

One sort key design serves multiple levels of hierarchy.

Single-Table Design Example:
═══════════════════════════════════════════════════════════════
PK              | SK                    | Type    | Data
═══════════════════════════════════════════════════════════════
USER#123        | USER#123              | User    | {name, email, ...}
USER#123        | ORDER#2024-01-15#abc  | Order   | {total, status, ...}
USER#123        | ORDER#2024-01-20#def  | Order   | {total, status, ...}
USER#123        | FOLLOWER#456          | Follow  | {followedAt, ...}
USER#456        | USER#456              | User    | {name, email, ...}
USER#456        | FOLLOWED_BY#123       | Follow  | {followedAt, ...}
ORDER#abc       | ITEM#001              | Item    | {product, qty, ...}
ORDER#abc       | ITEM#002              | Item    | {product, qty, ...}
═══════════════════════════════════════════════════════════════

Queries enabled:
  • Get user profile:     PK = USER#123, SK = USER#123
  • Get user's orders:    PK = USER#123, SK begins_with "ORDER#"
  • Get recent orders:    PK = USER#123, SK begins_with "ORDER#", 
                          ScanIndexForward=false, Limit=5
  • Get order items:      PK = ORDER#abc, SK begins_with "ITEM#"
  • Get user's followers: PK = USER#123, SK begins_with "FOLLOWER#"

Key Overloading Example:
Same PK attribute holds different entity references:

PK = USER#123,  SK = USER#123        → User profile
PK = USER#123,  SK = ORDER#timestamp → User's order
PK = ORDER#abc, SK = ORDER#abc       → Order details
PK = ORDER#abc, SK = ITEM#001        → Order line item

The PK and SK columns are generic — their meaning depends on
the item type. An "entityType" attribute disambiguates.

Modeling many-to-many relationships:

PK = USER#123, SK = USER#123         → user item
PK = USER#123, SK = FOLLOWS#456      → user 123 follows user 456
PK = USER#456, SK = FOLLOWED_BY#123  → inverted relationship

Query who user follows:    PK = USER#123, SK begins_with "FOLLOWS#"
Query followers of user:   PK = USER#456, SK begins_with "FOLLOWED_BY#"

Both directions queryable without a join.

Parent-child relationships in one partition:

PK = ORG#acme, SK = ORG#acme                        → org item
PK = ORG#acme, SK = DEPT#engineering                 → department
PK = ORG#acme, SK = DEPT#engineering#USER#123        → user in dept

Query entire org:           PK = ORG#acme
Query specific department:  PK = ORG#acme, SK begins_with "DEPT#engineering"
Query users in department:  PK = ORG#acme, SK begins_with "DEPT#engineering#USER#"

Append-only writes, queries by time range:

PK = DEVICE#sensor1, SK = 2024-01-15T10:30:00Z
PK = DEVICE#sensor1, SK = 2024-01-15T10:31:00Z

Query last hour: PK = DEVICE#sensor1 
                 AND SK BETWEEN '2024-01-15T09:30' AND '2024-01-15T10:30'

⚠️ Hot partition risk: all writes go to current time.
Solution: shard by time bucket or add random suffix.

// Add version attribute to items
// UpdateItem condition: version must match expected
const params = {
  TableName: "Products",
  Key: { PK: "PRODUCT#123", SK: "PRODUCT#123" },
  UpdateExpression: "SET #name = :name, #version = #version + :one",
  ConditionExpression: "#version = :expectedVersion",
  ExpressionAttributeNames: { "#name": "name", "#version": "version" },
  ExpressionAttributeValues: { 
    ":name": "Updated Name",
    ":one": 1,
    ":expectedVersion": 5  // current known version
  }
};
// ConditionalCheckFailedException → retry with fresh read