Why put customers and orders in the same table instead of two separate tables?

Because the access pattern that matters most here — 'get a customer and every order they've placed' — becomes one Query against one partition key when both entity types share PK = CUSTOMER# . Two tables would mean a Query against the customer table plus a second Query (or BatchGetItem) against the orders table, and DynamoDB has no cross-table join to do that for you in one round trip.

How does one sort key support both an exact profile lookup and a date-range order query?

By prefixing: SK is either the literal string PROFILE or ORDER# # . A Query for PK alone returns everything under that customer, sorted by SK — PROFILE and every ORDER# item together. Narrowing SK with begins_with(SK, "ORDER#") or a BETWEEN range on the ORDER# prefix (this lesson's date-range pattern) selects just the orders, because DynamoDB sorts sort keys as plain strings and ISO dates sort correctly as strings.

Why does the GSI only contain some of the items in the table?

Because GSI1PK/GSI1SK are only set on order items, not on PROFILE items — this lesson's profile item has neither attribute. A Global Secondary Index is sparse: DynamoDB only projects an item into it if the item actually has every attribute the GSI's own key schema names. That's what keeps a 'query across all customers by status' index from also being cluttered with profile rows that have no status at all.

Is this really 'the' way to model this data, or just one possible design?

One possible design, chosen specifically to make this lesson's three stated access patterns each cost one Query. A real system's access patterns drive the actual key design — a reporting system that mostly scans everything, or one that needs orders sorted by total rather than date, would overload PK/SK differently. The discipline single-table design asks for is naming every access pattern first and then designing keys to serve them, not designing a schema and hoping the access patterns fit.

DynamoDB: Single-Table Design

Relational design starts from entities and normalizes; DynamoDB single-table design starts from access patterns and designs the partition key (PK) and sort key (SK) so each pattern costs one Query. This lesson models customers and their orders in one table against real DynamoDB Local, and proves three access patterns each really do cost one Query — including one that spans every customer.

One Table, Two Entity Types, One GSI

PK/SK are generic names on purpose — what they mean depends on a prefix. A customer’s own row is SK = "PROFILE"; each of their orders is SK = "ORDER#<date>#<orderId>", so sorting by SK naturally groups and date-orders a customer’s orders. GSI1PK/GSI1SK exist only on order items, keyed by status, for the cross-customer pattern later.

#!/usr/bin/env bash
set -e
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws20-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@" --endpoint-url http://aws20-ddb:8000; }

aws dynamodb create-table --table-name Store \
  --attribute-definitions \
    AttributeName=PK,AttributeType=S AttributeName=SK,AttributeType=S \
    AttributeName=GSI1PK,AttributeType=S AttributeName=GSI1SK,AttributeType=S \
  --key-schema AttributeName=PK,KeyType=HASH AttributeName=SK,KeyType=RANGE \
  --global-secondary-indexes '[{"IndexName":"GSI1","KeySchema":[{"AttributeName":"GSI1PK","KeyType":"HASH"},{"AttributeName":"GSI1SK","KeyType":"RANGE"}],"Projection":{"ProjectionType":"ALL"}}]' \
  --billing-mode PAY_PER_REQUEST >/dev/null
echo "table created"

aws dynamodb put-item --table-name Store --item \
  '{"PK":{"S":"CUSTOMER#alice"},"SK":{"S":"PROFILE"},"name":{"S":"Alice"},"email":{"S":"alice@example.com"}}' >/dev/null
aws dynamodb put-item --table-name Store --item \
  '{"PK":{"S":"CUSTOMER#alice"},"SK":{"S":"ORDER#2026-01-05#o-100"},"status":{"S":"shipped"},"total":{"N":"30"},"GSI1PK":{"S":"STATUS#shipped"},"GSI1SK":{"S":"ORDER#2026-01-05#o-100"}}' >/dev/null
aws dynamodb put-item --table-name Store --item \
  '{"PK":{"S":"CUSTOMER#alice"},"SK":{"S":"ORDER#2026-02-10#o-101"},"status":{"S":"pending"},"total":{"N":"55"},"GSI1PK":{"S":"STATUS#pending"},"GSI1SK":{"S":"ORDER#2026-02-10#o-101"}}' >/dev/null
aws dynamodb put-item --table-name Store --item \
  '{"PK":{"S":"CUSTOMER#alice"},"SK":{"S":"ORDER#2026-03-01#o-102"},"status":{"S":"pending"},"total":{"N":"18"},"GSI1PK":{"S":"STATUS#pending"},"GSI1SK":{"S":"ORDER#2026-03-01#o-102"}}' >/dev/null

aws dynamodb put-item --table-name Store --item \
  '{"PK":{"S":"CUSTOMER#bob"},"SK":{"S":"PROFILE"},"name":{"S":"Bob"},"email":{"S":"bob@example.com"}}' >/dev/null
aws dynamodb put-item --table-name Store --item \
  '{"PK":{"S":"CUSTOMER#bob"},"SK":{"S":"ORDER#2026-02-20#o-200"},"status":{"S":"pending"},"total":{"N":"12"},"GSI1PK":{"S":"STATUS#pending"},"GSI1SK":{"S":"ORDER#2026-02-20#o-200"}}' >/dev/null
echo "2 customers, 4 orders written"

$ docker network create aws20-lab
5e94d72cbf90302e91256164800a0654defdb4148205646fcb5cec34e144e4e0
$ docker run -d --name aws20-ddb --network aws20-lab amazon/dynamodb-local:3.3.2
10ab4f327a76ea69c6fe478d99c3acf17a05e2bf97ab416dbb88eb54f3c0fa17
$ until docker run --rm --network aws20-lab curlimages/curl:8.11.1 -s -o /dev/null http://aws20-ddb:8000; do sleep 0.3; done
$ bash setup.sh
table created
2 customers, 4 orders written

Pattern 1: A Customer’s Profile and Every Order, One Query

#!/usr/bin/env bash
set -e
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws20-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@" --endpoint-url http://aws20-ddb:8000; }

aws dynamodb query --table-name Store \
  --key-condition-expression "PK = :pk" \
  --expression-attribute-values '{":pk":{"S":"CUSTOMER#alice"}}' \
  --return-consumed-capacity TOTAL \
  --query '{Count:Count,Items:Items[].SK.S,RCU:ConsumedCapacity.CapacityUnits}'

$ bash pattern1.sh
{
    "Count": 4,
    "Items": [
        "ORDER#2026-01-05#o-100",
        "ORDER#2026-02-10#o-101",
        "ORDER#2026-03-01#o-102",
        "PROFILE"
    ],
    "RCU": 0.5
}

One Query on PK = "CUSTOMER#alice" returns PROFILE and all 3 of alice’s orders together — SK’s own alphabetical sort puts the ORDER# items (starting with the letter O) before PROFILE (P), but every item sharing the partition key comes back from a single request either way. ConsumedCapacity reporting one number (RCU: 0.5) for all four items is the measured proof of that: a second Query call would show up as a second, separate ConsumedCapacity entirely.

Pattern 2: A Date Range of Orders, One Query

#!/usr/bin/env bash
set -e
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws20-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@" --endpoint-url http://aws20-ddb:8000; }

aws dynamodb query --table-name Store \
  --key-condition-expression "PK = :pk AND SK BETWEEN :start AND :end" \
  --expression-attribute-values '{":pk":{"S":"CUSTOMER#alice"},":start":{"S":"ORDER#2026-02-01"},":end":{"S":"ORDER#2026-02-28"}}' \
  --return-consumed-capacity TOTAL \
  --query '{Count:Count,Items:Items[].SK.S,RCU:ConsumedCapacity.CapacityUnits}'

$ bash pattern2.sh
{
    "Count": 1,
    "Items": [
        "ORDER#2026-02-10#o-101"
    ],
    "RCU": 0.5
}

Only the February order comes back — BETWEEN on SK works here because ORDER#2026-02-10#o-101 sorts, as a plain string, between ORDER#2026-02-01 and ORDER#2026-02-28. Embedding an ISO-formatted date directly in the sort key makes a string range comparison stand in for a date range, but it’s a string comparison — Practice 5 finds a real off-by-one this introduces at the range’s own end boundary.

Pattern 3: Every Pending Order, Across Every Customer, One Query

#!/usr/bin/env bash
set -e
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws20-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@" --endpoint-url http://aws20-ddb:8000; }

aws dynamodb query --table-name Store --index-name GSI1 \
  --key-condition-expression "GSI1PK = :s" \
  --expression-attribute-values '{":s":{"S":"STATUS#pending"}}' \
  --return-consumed-capacity TOTAL \
  --query '{Count:Count,Items:Items[].{Customer:PK.S,Order:SK.S},RCU:ConsumedCapacity.CapacityUnits}'

$ bash pattern3.sh
{
    "Count": 3,
    "Items": [
        {
            "Customer": "CUSTOMER#alice",
            "Order": "ORDER#2026-02-10#o-101"
        },
        {
            "Customer": "CUSTOMER#bob",
            "Order": "ORDER#2026-02-20#o-200"
        },
        {
            "Customer": "CUSTOMER#alice",
            "Order": "ORDER#2026-03-01#o-102"
        }
    ],
    "RCU": 0.5
}

Three pending orders come back — two of alice’s, one of bob’s — from a single Query against GSI1 (one RCU: 0.5 again), with no Scan anywhere. The base table’s own key (PK/SK) has no way to ask “every pending order regardless of customer”; GSI1 exists specifically because this third access pattern needed its own partition key shape.

Practice

1. Fetch just alice's profile with GetItem instead of Query, using the exact SK "PROFILE".

#!/usr/bin/env bash
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws20-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@" --endpoint-url http://aws20-ddb:8000; }

aws dynamodb get-item --table-name Store \
  --key '{"PK":{"S":"CUSTOMER#alice"},"SK":{"S":"PROFILE"}}' \
  --query '{name:Item.name.S,email:Item.email.S}'

$ bash practice1.sh
{
    "name": "Alice",
    "email": "alice@example.com"
}

GetItem with the exact PK+SK needs the caller to already know exactly which one item it wants — for a single small item it costs the same as a one-item Query (both one eventually-consistent read unit), so the real advantage here is precision, not price, the same point lesson 11 made, still true inside a single-table design.

2. Query GSI1 for a status no order has (e.g. "cancelled") and confirm it's a normal empty result, not an error.

#!/usr/bin/env bash
set -e
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws20-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@" --endpoint-url http://aws20-ddb:8000; }

aws dynamodb query --table-name Store --index-name GSI1 \
  --key-condition-expression "GSI1PK = :s" \
  --expression-attribute-values '{":s":{"S":"STATUS#cancelled"}}' \
  --query '{Count:Count}'

$ bash practice2.sh
{
    "Count": 0
}

Count: 0 — querying a GSI partition key that no item happens to have is exactly as normal as querying a base-table partition key with no matches, not a special case.

3. Add a new order for a third customer and confirm GSI1 picks it up with no index-maintenance step of your own.

#!/usr/bin/env bash
set -e
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws20-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@" --endpoint-url http://aws20-ddb:8000; }

aws dynamodb put-item --table-name Store --item \
  '{"PK":{"S":"CUSTOMER#carol"},"SK":{"S":"ORDER#2026-03-15#o-300"},"status":{"S":"pending"},"total":{"N":"40"},"GSI1PK":{"S":"STATUS#pending"},"GSI1SK":{"S":"ORDER#2026-03-15#o-300"}}' >/dev/null

aws dynamodb query --table-name Store --index-name GSI1 \
  --key-condition-expression "GSI1PK = :s" \
  --expression-attribute-values '{":s":{"S":"STATUS#pending"}}' \
  --query 'Count'

$ bash practice3.sh
4

4, not 3 — carol’s new order appears in GSI1 right away here, the same automatic sync lesson 12 showed for a base-table GSI (and the same caveat: that sync is eventually consistent on real AWS, so this immediacy is DynamoDB Local’s fast propagation, not a guarantee), now proven inside a multi-entity single-table design too.

4. Query alice's PROFILE item through GSI1 and confirm it never shows up there (the index is sparse).

#!/usr/bin/env bash
set -e
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws20-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@" --endpoint-url http://aws20-ddb:8000; }

aws dynamodb scan --table-name Store --index-name GSI1 \
  --filter-expression "PK = :pk AND SK = :sk" \
  --expression-attribute-values '{":pk":{"S":"CUSTOMER#alice"},":sk":{"S":"PROFILE"}}' \
  --query 'Count'

$ bash practice4.sh
0

0 — alice’s PROFILE item has no GSI1PK/GSI1SK attributes at all, so DynamoDB never projects it into GSI1 in the first place; scanning the index directly for it, rather than the base table, confirms it just isn’t there to find.

5. Add an order dated exactly on Pattern 2's end boundary (2026-02-28), confirm the naive BETWEEN bound silently excludes it, then fix the bound.

#!/usr/bin/env bash
set -e
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws20-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@" --endpoint-url http://aws20-ddb:8000; }

aws dynamodb put-item --table-name Store --item \
  '{"PK":{"S":"CUSTOMER#dave"},"SK":{"S":"ORDER#2026-02-28#o-400"},"status":{"S":"pending"},"total":{"N":"25"}}' >/dev/null

echo "naive bound (end = ORDER#2026-02-28, no suffix):"
aws dynamodb query --table-name Store \
  --key-condition-expression "PK = :pk AND SK BETWEEN :start AND :end" \
  --expression-attribute-values '{":pk":{"S":"CUSTOMER#dave"},":start":{"S":"ORDER#2026-02-01"},":end":{"S":"ORDER#2026-02-28"}}' \
  --query 'Count'

echo "fixed bound (end = ORDER#2026-02-28~, sorts after any same-day suffix):"
aws dynamodb query --table-name Store \
  --key-condition-expression "PK = :pk AND SK BETWEEN :start AND :end" \
  --expression-attribute-values '{":pk":{"S":"CUSTOMER#dave"},":start":{"S":"ORDER#2026-02-01"},":end":{"S":"ORDER#2026-02-28~"}}' \
  --query 'Count'

$ bash practice5.sh
naive bound (end = ORDER#2026-02-28, no suffix):
0
fixed bound (end = ORDER#2026-02-28~, sorts after any same-day suffix):
1

Count: 0 with the naive bound — "ORDER#2026-02-28#o-400" is a longer string than "ORDER#2026-02-28", and a string that extends a prefix always sorts after that bare prefix, so the order lands just past the upper bound and gets silently excluded, even though it’s dated exactly on the boundary day. Appending ~ (a character that sorts after any digit, letter, or #/- an order ID would use) to the end bound fixes it to Count: 1 — the right fix for a sort-key date range is to make the end bound sort after every possible item on the last day, not just after the bare date string.

Cleanup

$ docker rm -f aws20-ddb
aws20-ddb
$ docker network rm aws20-lab
aws20-lab

Programming

Web Development

Generative AI

Machine Learning

Cybersecurity

Data Engineering

Cloud

DevOps

System Design

GPU & Parallel

Databases

Testing

Interviews