I have a requirement to store order updates composed of the following data and trying to design table structure(s) to support the query patterns.
order_id: bigint update_date: timestamp lat: double long: double volume: int duration: int
My query patterns will be:
1. retrieve record(s) by a specific order_id,
2. retrieve records across a range of update_date (irrespective of orders)
So far, I have come up with the following two tables to support these two queries.
partition key = order_id,
clustering key = update_date
partition key = month_and_year,
clustering key = update_date, order_id
month_and_year is a new column I've added to partition the orders_by_month_and_year table by the values like yyyy-mm so that my range queries will be run on a single partition identified by the month_and_year column as the equality search constraint in the query along with the range applied on update_date column.
Does this look like an optimal design for the tables or do you think it can be improved?
Thanks in advance!