CODEWITHSUNDEEP
amazon-dynamodbdynamodb-queries
Marcelo MD
Marcelo MD

Reputation: 1769

How to select partition and sort keys for hierarchical model

Please advise if this is not the right place to ask this.

I'm trying to find a suitable partition key/sort key pair for a simple relational model (a seller has many customers, a customer has many orders) that covers the following query patterns:

My order ids are in the form yyyy-mm-dd-random_string, so it is sortable by date. There is only one item per order.

Using seller_id as partition key, and customer_id#order_id as sort key covers the first use case, but there is no way to "jump" over the customers.

How can I get these two queries from the same records?

Bonus:

Upvotes: 0

Views: 187

Answers (1)

Matthew Pope
Matthew Pope

Reputation: 7679

To support your second query, you need to create a secondary index on seller_id and order_id.

There is a way to do it without secondary indexes, but it would require a full table scan.

The general approach to “jumping” over fields in a hierarchical key schema is to create a GSI with a key that omits the field that you are “jumping”.

Upvotes: 1

Related Questions