Bringing together the Apache Cassandra experts from the community and DataStax.

Want to learn? Have a question? Want to share your expertise? You are in the right place!

Not sure where to begin? Getting Started

 

question

ginoymg_130837 avatar image
ginoymg_130837 asked Erick Ramirez commented

How do I model my tables for equality and range queries?

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.

Table: order_updates_by_orderid

partition key = order_id,
clustering key = update_date

Table: orders_by_month_and_year

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!

data modeling
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

2 Answers

Erick Ramirez avatar image
Erick Ramirez answered Erick Ramirez commented

Both these data models look reasonable to me:

CREATE TABLE order_updates_by_orderid (
    PRIMARY KEY (order_id, update_date)
)
CREATE TABLE orders_by_month_and_year (
    month_and_year text,
    update_date timestamp,
    order_id bigint,
    lat double,
    long double,
    volume int,
    duration int,
    PRIMARY KEY (month_and_year, update_date, order_id)
)

Cheers!

2 comments Share
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

ginoymg_130837 avatar image ginoymg_130837 ·

Thanks so much for your reviews and comments

0 Likes 0 ·
Erick Ramirez avatar image Erick Ramirez ♦♦ ginoymg_130837 ·

Not a problem. Cheers!

0 Likes 0 ·
laxmikant.hcl_32751 avatar image
laxmikant.hcl_32751 answered Erick Ramirez commented

Looks ok.. Just make sure your orders_by_month_and_year table does not have partition size more than 100 MB .

1 comment Share
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Erick Ramirez avatar image Erick Ramirez ♦♦ ·

Thanks for your contributions to the community. Keep going! :)

0 Likes 0 ·