I wish to execute the following query:
SELECT value,occurredAt,venueName FROM Transaction_by_card WHERE card = 1234123412341234
and expect a few hundred results for each card, as is your regular credit card bill.
I was thinking about modelling the table like:
(card int, occurredAt timestamp, venueName text, value int PRIMARY KEY (card,timestamp) WITH CLUSTERING ORDER BY occurredAt desc)
Is that the best model for my use case?
Well just make sure that your total records per card should be less than 100 mb (kind of thumb rule in Cassandra) but i would prefer much less value than this.
If you assume that your result size can be more you can do time based baucketing
For example a month based bucket will look like
(card int, occurredAt timestamp, year int, month int, venueName text, value int PRIMARY KEY ( (card, year, month) timestamp) WITH CLUSTERING ORDER BY occurredAt desc)
month:04 and year 2020 will be derived from occurredAt timstampp.
