Table Design Interview Questions

Access patterns come first: every user-facing read and write, the key you will Query on, and expected QPS. DynamoDB has no cheap joins, so a schema that looks tidy in 3NF often forces Scans or extra round-trips. Interviewers want that list, not an ER diagram copied from Postgres.

Store child items in the same partition as the parent, using a sort key that identifies the child (ORDER#<id>, MESSAGE#<ts>). A Query with begins_with returns the collection in one call. Putting children in a separate table is fine only when you never need them in the same request path.

Duplicate fields you must read without a second Query—display names, status labels, or a denormalized total. You pay with extra writes when the source of truth changes, so copy only stable or carefully updated values. Never duplicate large blobs just to make a row look like a SQL join result.

Several entity types share one table with overloaded PK/SK values so related items can be fetched together. It pays off when access patterns are known and the team can own generic keys and GSIs. Multiple tables stay clearer when bounded contexts, IAM, or backup cadences differ.

A type prefix on the sort key (USER#, ORDER#, PAYMENT#) plus an attribute such as entityType lets Query and Filters tell items apart. GSIs can project only the types they need. Without prefixes, mixed entities collide on keys and FilterExpressions turn into hidden Scans.

Scan cost and latency grow with table size, and filters still read every item. User-facing paths should be Query by partition key. If you cannot name the key for a request, the table is not designed yet—add a GSI or a new item shape rather than scanning.

Names like PK and SK let one table hold many entity types without a column per relationship. Application code writes typed values (TENANT#42, ORDER#1001) into those two attributes. The physical table stays simple; the meaning lives in the key conventions you document.

Prefer a new GSI or a new item type that you dual-write, then backfill. Changing the primary key of existing items is a migration, not an ALTER COLUMN. Feature-flag the new Query path and keep the old one until the backfill and lag checks pass.

Add a schemaVersion field and make readers tolerant of missing new attributes. Writers can roll forward per item on the next Update. Avoid rewriting the whole table in one job unless you have a measured backfill window and a rollback that does not strand mixed versions.

Split when you approach the 400 KB limit, when one huge document makes every read expensive, or when different fields change at different rates. Keep a stub item for the identity and child items for history or large collections. Splitting only “for purity” adds round-trips with no gain.

Each required Query that the primary key cannot serve should be a named GSI with its own PK/SK and projection. Designing GSIs last often means duplicating the whole item (ALL) and overpaying writes. Sparse and overloaded GSIs are table-design tools, not indexing afterthoughts.

A dedicated item holds a counter or summary you cannot afford to compute with a wide Query on every read—unread count, order total, leaderboard score. Update it in the same transaction or with a disciplined single-item add. Stale aggregates need a rebuild path when the counter drifts.

Colocate only what is queried together and is not a celebrity entity. A global product or tenant-root partition that every request touches will throttle even if the model looks elegant. Spread write-heavy children by a higher-cardinality key and Query them on a GSI when needed.

Use two item types or an adjacency-list edge item (USER#id / COURSE#id and the inverse) so each side can Query its links. Avoid arrays of IDs that grow past item size. Edge items can carry payload such as enrolledAt without joining a third table.

Put tenantId into every partition key so isolation is physical, not a filter. Cap per-tenant write rates in the app and watch Contributor Insights for hot tenant keys. Shared partitions that mix all tenants make one customer’s burst everyone else’s incident.

Expand/contract: dual-write both stores, backfill historical rows, shadow-read DynamoDB, then cut reads and stop SQL writes. Keep a reversible flag and compare item counts and checksums. A big-bang dump-and-switch usually misses in-flight orders and unique-key assumptions SQL was enforcing.

Archive old sort-key ranges to another table or S3, use TTL on event items, or roll time windows into new partitions (userId#2026-09). Unbounded collections under one PK make every “list recent” Query more expensive over years. Design the rollover before the first million items land.

Readers see contradictory names, prices, or statuses depending on which item they hit. Mitigate with a single writer path, transactions when two items must stay aligned, or treating one item as source of truth and others as caches with TTL. Drift is a design bug, not an eventual-consistency footnote.

Keep the operational table Query-shaped, then export to S3/Athena or stream changes to a warehouse. Parallel Scan on production for dashboards steals capacity from users. If a product metric must be real-time, maintain a small aggregate item rather than scanning orders.

Named access patterns with sample keys, predicted QPS per key, item size budget, GSI list with projections, failure modes for hot keys, backup/PITR, and a load test that hits realistic key cardinality. Shipping “we will add indexes later” is how first-week throttling incidents start.