How to select partition and sort keys for hierarchical model

Question

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:

For a given seller, get all orders for a given customer in a date range;
For a given seller, get all orders for all customers in a date range.

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:

Is there a way to do it without secondary indexes?
Is there a general approach for "jumping" over hierarchical relations?

Are you aware of the 'begins_with' function that you can use for key condition expressions? — Matthew Pope
– Matthew Pope, Commented Sep 23, 2019 at 16:29
@MatthewPope Yes. With the schema proposed in the question I can query begins_with("customer_id#2019") and it gets me all the orders for a customer for year 2019. But how can I get all orders placed in 2019 for all customers? Something like /.*#2019.*/. — Marcelo MD
– Marcelo MD, Commented Sep 26, 2019 at 22:50

Matthew Pope · Accepted Answer · 2019-09-28 16:10:30Z

1

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”.

answered Sep 28, 2019 at 16:10

Matthew Pope

7,7292 gold badges34 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marcelo MD Over a year ago

Thanks. I was hoping there was an idiom or a pattern for queries like this without having to create a second index. Oh well...

Collectives™ on Stack Overflow

How to select partition and sort keys for hierarchical model

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related