DVA-C02 Question 98
Select 2You are designing a DynamoDB table to store information about customer orders. Each order has a unique OrderID, a CustomerID to identify the customer who placed the order, and an OrderDate to represent when the order was placed. You need to retrieve all orders placed by a specific customer in a sorted order based on the OrderDate. How should you define the primary key and indexes for this table?
- A
Use OrderID as the partition key and create a Global Secondary Index (GSI) with CustomerID as the partition key and OrderDate as the sort key.
- B
Use CustomerID as the partition key and OrderDate as the sort key in the table's primary key.
- C
Use OrderID as the partition key and OrderDate as the sort key in the table's primary key.
- D
Use CustomerID as the partition key and create a Local Secondary Index (LSI) with OrderDate as the sort key.
- E
Use CustomerID as the partition key and create a Global Secondary Index (GSI) with OrderDate as the sort key.
Show answer and explanation
Correct answers: B, D
Explanation
To retrieve all orders placed by a specific customer in a sorted order based on OrderDate, the table's primary key should use CustomerID as the partition key and OrderDate as the sort key, as this supports the query requirements directly. Alternatively, an LSI can be used with CustomerID as the partition key and OrderDate as the sort key, which also allows querying within the same partition key while sorting by a secondary attribute.
- A. Incorrect.
Using OrderID as the partition key and creating a GSI with CustomerID as the partition key and OrderDate as the sort key would allow querying by CustomerID and sorting by OrderDate. However, this is unnecessary if the primary key is designed correctly to meet the query requirements.
- B. Correct.
Using CustomerID as the partition key and OrderDate as the sort key in the table's primary key is a good choice because it directly supports querying all orders of a specific customer sorted by OrderDate without requiring additional indexes.
- C. Incorrect.
Using OrderID as the partition key and OrderDate as the sort key in the table's primary key does not support querying by CustomerID. OrderID is unique to each order and does not group orders by customer.
- D. Correct.
Using CustomerID as the partition key and creating a Local Secondary Index (LSI) with OrderDate as the sort key is a valid solution because LSIs allow querying within the same partition key and sorting based on a secondary attribute (OrderDate).
- E. Incorrect.
Using CustomerID as the partition key and creating a GSI with OrderDate as the sort key is not an efficient choice for this scenario because GSIs are designed for querying across all partition keys, whereas this use case focuses on querying by CustomerID.