DynamoDB single table design: what clicked for me after getting it wrong first

Every time I read about single table design it sounded like magic: one table, several entities, everything solved with well-chosen keys. Even the official AWS NoSQL design guide  makes it look simple on paper. Modeling a small app of my own, the learning curve was rougher than the articles suggested.

I started designing as if it were a relational database, separate tables in my head for users, orders, and items, only later trying to fit that into partition and sort keys. That produced a model that needed up to four queries just to render one order detail screen.

What unblocked things was stopping to list every access pattern I actually needed, like fetching an order with its items in one query, or a customer's most recent orders by date. That's the same core advice from an AWS Compute blog post on single table design  that I only found after already learning it the hard way. A customer and their orders ended up sharing the same partition key, CUSTOMER#42, with the sort key telling entities apart: a PROFILE sort key holding name, email, and join date, and one ORDER#<date> sort key per order holding its total, status, and item count.

A single query for PK equals CUSTOMER#42 with a sort key starting with ORDER pulls back every order already sorted by date, no join needed.

I still occasionally forget to plan for global secondary indexes early, and only notice when a new query requirement shows up, like wanting a report of orders by status across all customers, which the existing keys can't answer without a full scan. If you're just starting with this pattern, spending more time listing queries than designing the schema seems to pay off, since the schema mostly falls out of that list once it's written down.