I checked out a lot of DynamoDB tutorials and SO questions, but none of them covers my use case.
I have 3 columns:
- Category
- Subcategory
- Item
Subcategories belongs to a Category, and the item is the only unique value.
I have read that (3 fields composite primary key (unique item) in Dynamodb) I should design my DynamoDB table based on what type of queries I want to perform:
- Get all items within a Category
- Get all items within a Subcategory
My first approach would be:
- Category as a Partition key
- Subcategory_Item as a Sort key
Category + Subcategory is not unique, so thats why I concatenated the Item to the Subcategory.
Since I usually want to query Items within the same Category together, it is good to have the same Category items within the same partition also sorted by the subcategory, so this way (If I get it right) my "Get all items within a Subcategory" will perform good and I can easily query it based on the sortkey with "begins_with(subcategory)"
The drawback this way would be that after I obtained data from Dynamo I would need to aggregate over it to separate the Item name from the Subcategory name.
My second approach:
- Category as partition key
- Item as Sort key
- Subcategory as Attribute
- Local Secondary Index for Subcategory
So this way the "Get all items within a Category" query will be a simple, items within the same category will be on the same partition also, and thanks to the LSI the "Get all items within a Subcategory" will be also simple and fast.
My questions:
- Is there any drawback to the second approach?
- Is there any better approach than these?
