Thanks Ryan, Srinivas for you answer. Finally I have decided to create three column families
1. product_date_id (yyyymm, dd, prodid) PRIMARY KEY ((yyyymm), dd, prodid) - Record the arrival date on updates of a product - Get list of products that are recently added/updated Ex: [(yyyymm, dd) > (2014, 06)] 1. product_status(prodid int, status int) PRIMARY KEY (prodid), INDEX on (status) - Each time I add a product just insert a record (prodid, defstatus) with the condition IF NOT EXISTS, to avoid status being updated, Here I couldnt avoid read before write to protect product status - Update Enable/Disable prodid - Get list of product ids with the give status 2. product_details(prodgrp, prodid, ..... <other details of product>) PRIMARY KEY (prodgrp, prodid) - Insert product details in the prodgrp blindly to store recent updates of the product details - Get list of products in the product group - Get details of products for the give ids "get list of products for a given range of ids" : My queries are answered with the above design. PS: I am still thinking to avoid read before write on product_status. And would like to see if there is better way to design using supercolumn families or materialized views which I am yet to explore. Regards, Nageswara Rao On Tue, Jan 6, 2015 at 10:53 PM, Ryan Svihla <r...@foundev.pro> wrote: > Normal data modeling approach in Cassandra is a separate column family of > each of those queries is answerable with one partition key (that's going to > be the fastest). > > I'm very suspicious of > > - get list of products for a given range of ids > > Is this being driven by another query to get a list of ids? If so that > should probably be modeled differently and any query that would normally > return a list of ids should instead be modeled to produce a full product > (materialized views scale very well on any database and it's the common > approach on Cassandra) > > > On Mon, Jan 5, 2015 at 6:18 AM, Srinivasa T N <seen...@gmail.com> wrote: > >> Just an arrow in the dark: Doucment "CQL for Cassandra 2.x Documentation" >> informs that cassandra allows to query on a column when it is indexed. >> >> Regards, >> Seenu. >> >> On Mon, Jan 5, 2015 at 5:14 PM, Nagesh <nageswara.r...@gmail.com> wrote: >> >>> Hi All, >>> >>> I have designed a column family >>> >>> prodgroup text, prodid int, status int, , PRIMARY KEY ((prodgroup), >>> prodid, status) >>> >>> The data model is to cater >>> >>> - Get list of products from the product group >>> - get list of products for a given range of ids >>> - Get details of a specific product >>> - Update status of the product acive/inactive >>> - Get list of products that are active or inactive (select * from >>> product where prodgroup='xyz' and prodid > 0 and status = 0) >>> >>> The design works fine, except for the last query . Cassandra not >>> allowing to query on status unless I fix the product id. I think defining a >>> super column family which has the key "PRIMARY KEY((prodgroup), staus, >>> productid)" should work. Would like to get expert advice on other >>> alternatives. >>> -- >>> Thanks, >>> Nageswara Rao.V >>> >>> *"The LORD reigns"* >>> >> >> > > > -- > > Thanks, > Ryan Svihla > > -- Thanks, Nageswara Rao.V *"The LORD reigns"*
