Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 37 ยท Data Modeling Patterns

Single-Table Design

Store several entity types in one DynamoDB table with generic PK and SK attributes, fetch related items in one Query, and learn when multiple tables are fine.

A relational database keeps one table per entity: customers, orders, order lines. To show an order page you join three of them. DynamoDB cannot join, so reading three tables means three network calls. Single-table design removes the calls: you store customers, orders and order lines in the same table, arranged so that everything one screen needs sits in one partition and arrives in one Query.

This is the technique people mean when they say "DynamoDB modeling is different". It looks odd at first, with attributes named PK and SK and values like ORDER#o21. This lesson explains why it works, how to design the keys, and when you should not do it.

Item collections

All items that share a partition key value are called an item collection. DynamoDB keeps them physically together and sorted by sort key. If you give an order and its lines the same partition key, one Query returns them all:

PK SK Attributes
ORDER#o21 META customerId, status, total
ORDER#o21 LINE#001 sku, qty, price
ORDER#o21 LINE#002 sku, qty, price

Query PK = ORDER#o21 returns the header and both lines, already ordered, in a single request that reads one partition. A relational join would have touched three tables and an index.

One table, many shapes

Generic keys PK and SK hold any entity
Type prefixes ORDER#, LINE#, USER#
Item collection Same PK, sorted by SK

Related items live side by side.

1 / 2

Designing the keys

Follow these rules and most designs work out:

  1. Name the keys generically. Use PK and SK (and GSI1PK, GSI1SK for an index) because a column can hold different meanings per item type.
  2. Prefix values with the entity type: CUSTOMER#c1, ORDER#o21. Prefixes prevent clashes (a customer and an order could share the ID 21) and make items self-describing in the console.
  3. Put what you query together under one PK, and order it with the SK. A begins_with condition on the SK then selects a type or a range.
  4. Add a Type attribute (or entity) so code and streams can tell items apart.
  5. Keep a key map: a table in your repository that lists each entity, its PK, SK and which pattern uses it.

An example key map for a shop:

Entity PK SK GSI1PK GSI1SK
Customer CUSTOMER#<id> PROFILE
Order CUSTOMER#<id> ORDER#<date>#<id> ORDER#<id> ORDER#<id>
Order line ORDER#<id> LINE#<n>

The order appears under its customer for "list my orders", and also under the order ID in an index for "get an order by ID" when you do not know the customer.

Try it: one query, many entity types

The stand-in below mimics a table with PK and SK (a Python dictionary; this is not the real service). Items of two types share one partition.

Python An item collection in one query

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The whole order page cost one request. In a multi-table design with three entities it would have cost at least two.

Overloading an index

You can also overload a global secondary index. A GSI has its own key attributes (GSI1PK, GSI1SK). Different entity types can fill them with different meanings, so one index serves several patterns. An order might set GSI1PK = STATUS#SHIPPED for the warehouse view, while a customer sets GSI1PK = EMAIL#[email protected] for login lookup. Items without the attributes are simply absent from the index (a sparse index).

Python One overloaded index, two patterns

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The real service maintains the index for you, copies the projected attributes and updates it asynchronously. Indexes are covered earlier in the course; here, notice that one index answers two unrelated questions.

Trade-offs and when to use multiple tables

Single-table design is not free.

  • Readability. SK = LINE#001 means nothing to a newcomer without the key map. Document it.
  • Changing patterns. A new access pattern may force a new GSI or a data migration (read, transform, write). Plan for it.
  • Tooling. Analytics tools, exports and per-entity capacity tuning are harder when everything is mixed. A stream consumer needs the Type attribute to route items.
  • Different throughput or lifecycle. An entity that is 100 times busier than the rest, needs a different TTL, backup policy or encryption key may deserve its own table.

A pragmatic rule: use one table per service or bounded context, and put together the entities that are read together. A tiny app with one or two patterns does not need to be "clever". Choose clarity when no pattern needs a join.

Single-table design is a technique, not a requirement. AWS documentation presents it as one valid option. Pick it when it removes round trips for your real patterns.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is an item collection?

    Items with the same partition key are stored together and sorted by sort key. A Query on that key returns the collection.
  2. 2. Why do single-table designs use generic names like PK and SK?

    Key attribute names are fixed per table, so generic names let each entity type decide what the value means, usually with a prefix.
  3. 3. What is the main benefit of putting an order and its lines under one partition key?

    Related items arrive in one request that reads a single partition, so there is no join and no extra round trips.
  4. 4. When might a separate table be the better choice?

    Capacity, TTL, backups and permissions are set per table. Entities with very different needs are easier to run apart.

Summary

  • A single table stores several entity types; items with one partition key form an item collection.
  • Generic PK/SK names plus type prefixes (ORDER#, LINE#) keep keys unambiguous.
  • One Query can return an entity and its children, ordered by sort key.
  • Overloaded, sparse indexes let one GSI serve several patterns.
  • The cost is readability and migration effort, so keep a key map and use separate tables when needs differ.

Next: Relationships With Adjacency Lists