A city case-management app must fetch a case by caseId and also list a citizen’s cases by citizenId plus filing date. Why is a single partition key alone a poor fit?
Select an answer to reveal the explanation.
Short Explanation
Picture two different filing cabinets for the same stack of case folders. One lookup is “give me this case number”; the other is “give me everything this citizen filed last month.” One partition key alone can’t serve both efficiently—you design the base table for one pattern and add a GSI (or careful composite keys) for the other.
Full Explanation
DynamoDB key design starts from access patterns, not from a single “natural” identifier. The city case-management workload needs two efficient Query shapes: get one case by caseId, and list a citizen’s cases filtered or ordered by filing date. Those shapes disagree on which attribute should be the partition key and which should be the sort key. A high-cardinality caseId partition key (often with a related-entity sort key) serves the single-case fetch; listing by citizenId with a date range wants citizenId as the partition key and a filing-date (or composite date+caseId) sort key. The durable pattern is to optimize the base table for the primary pattern and add a global secondary index—or carefully designed composite keys—for the alternate pattern so both remain Queries rather than table scans.
Claiming DynamoDB forbids more than one attribute on every item confuses attribute flexibility with key design; items routinely carry many attributes beyond the keys. Treating caseId as inherently low cardinality is incorrect for municipal case numbers, which are typically unique and therefore good partition-key candidates for point lookups. Requiring an Amazon RDS foreign key to list by citizen invents a relational constraint DynamoDB does not use; secondary indexes and composite keys model the relationship without SQL FKs.
Exam caveat: “one partition key alone” is poor when patterns conflict—not because DynamoDB cannot store both attributes, but because Query efficiency depends on which attributes are keyed. Operational check: for a pilot citizenId, confirm a Query on the GSI (or alternate key schema) returns only that citizen’s cases in filing-date order within expected RCU, and that GetItem/Query by caseId still hits the base table without a Scan.