Performance is foremost in this architecture and so duplication of data is nessary.

By Product ID

The following is a simple product table used to lookup products by their product_id.

cqlsh> CREATE TABLE product (product_id text, name text, description text, asset text, category text, PRIMARY KEY((product_id)));

Populate with data:

cqlsh> UPDATE product SET name = 'Hammer', description = 'For nails', category = 'Tool' WHERE product_id = 'T112233';

Select a product:

cqlsh> SELECT * FROM product WHERE product_id = 'T112244';

By Category

We duplicate our data into another table with optimized promary key and column clustering.

cqlsh> CREATE TABLE product_by_category ( product_id text, name text, description text, asset text, category text, PRIMARY KEY((category), product_id, name)) WITH CLUSTERING ORDER BY (product_id ASC, name ASC);

cqlsh> UPDATE product_by_category SET description = 'For nails' WHERE product_id = 'T112233' AND category = 'Tool' AND name = 'Hammer';

cqlsh> UPDATE product_by_category SET description = 'For hammers' WHERE product_id = 'T112244' AND category = 'Hardware' AND name = 'Nails';