commerce
Commerce Capstone
Four entity types, one table, no joins and no scans.
The destination the rest of the library builds towards: customers, orders, line items and products in a single table, with a generic key carrying entity prefixes and one overloaded index answering the cross-cutting reads. Every technique here has appeared alone somewhere else.
The model
Order
An order header, in its customer's partition.
- pk
- CUSTOMER#<customerId>
- sk
- ORDER#<placedOn>#<orderId>
Attributes: status (S), totalPence (N), placedAt (N), GSI1PK (S), GSI1SK (S)
LineItem
One line of an order, in the same partition as its header.
- pk
- CUSTOMER#<customerId>
- sk
- ORDER#<placedOn>#<orderId>#LINE#<lineNo>
Attributes: sku (S), quantity (N), pricePence (N)
Customer
The customer header, sorting ahead of their orders.
- pk
- CUSTOMER#<customerId>
- sk
- CUSTOMER#<customerId>
Attributes: name (S), email (S)
Product
A product, in its own partition and in the shared index by category.
- pk
- PRODUCT#<sku>
- sk
- PRODUCT#<sku>
Attributes: title (S), pricePence (N), GSI1PK (S), GSI1SK (S)
Access patterns
- QueryEverything about a customer
The customer, their orders and every line, in one read.
- Query · GSI1Orders awaiting dispatch
Every order in one status, across all customers, oldest first.
- GetItemRead an order header
One order, without its lines.
Design notes
The key is generic so the items do not have to bewhy
PK and SK carry no domain meaning at all - they hold CUSTOMER#, ORDER#, PRODUCT# and whatever else the design needs later. That is what allows four entity types in one table: the key describes position, and the prefix describes what lives there. A table named per entity cannot do this, and needs a join to answer the first access pattern here.
One partition holds an entire order historywhy
The customer header, every order and every line share CUSTOMER#<id>, and the sort key nests lines under their order. So the order-history page is one Query: no join, no second round trip, and the rows arrive already grouped because an order sorts immediately before its own lines. The date sits in the key ahead of the order id, exactly as the Customer Orders model has it, so the collection is also in chronological order.
Sparse and overloaded at the same timewhy
Of the orders, only those awaiting dispatch carry GSI1PK, so the picking list is a sparse index - a delivered order leaves it by having its index keys removed. That same index also holds products under CATEGORY#, so one index serves the warehouse and the storefront. Both techniques appear alone elsewhere in this library; here they are the same index. The console runs one index read per model, so the category question is in the table to be seen rather than pressed.
What this costs to work withtrade-off
Nothing about this table is self-describing. A row's meaning lives in its prefix, a query has to know the key convention, and adding an access pattern usually means changing what is written rather than adding an index. That is the trade single-table design makes: the reads get cheap and fixed, and the design gets harder to change than a schema you can add a column to.
Start here lasttrade-off
Every technique on this page is one of the simpler models in this library, combined: item collections, a type-discriminating sort key, a sparse index, an overloaded index. Working out the access patterns first and the key design second is what makes the combination tractable - done the other way round, this table is impossible to get right.
Taught in the course
- One query, a whole collection - Items that share a partition key are read together in one Query.
- Absence as a filter - An item appears in an index only if it has the index's key, so leaving the key off filters it out.
- One index, many questions - Different entity types share generic index keys, so one index serves several access patterns.
- One table, an application's worth of questions - Generic keys and one overloaded index answer five access patterns with no join and no scan.
The customer, their orders and every line, in one read.
Run an operation to see the raw engine response.
The in-browser engine is a preview build. Transactions, vector search, streams, tags and TTL are among the operations it doesn't implement yet.