Shopping Cart Data Modeling Example

Create a keyspace

Create the shopping_cart_data keyspace:

CREATE KEYSPACE shopping_cart_data
WITH replication = {
  'class': 'NetworkTopologyStrategy', 
  'DC-Houston': 1 };

Set the current working keyspace:

USE shopping_cart_data;

Create tables

Create table carts_by_user:

CREATE TABLE carts_by_user (
  user_id TEXT,
  cart_name TEXT,
  cart_id UUID,
  cart_is_active BOOLEAN,
  user_email TEXT STATIC,
  PRIMARY KEY ((user_id),cart_name,cart_id)
);

Create table items_by_id:

CREATE TABLE items_by_id (
  id TEXT,
  name TEXT,
  description TEXT,
  price DECIMAL,
  PRIMARY KEY ((id))
);

Create materialized view items_by_name:

CREATE MATERIALIZED VIEW items_by_name 
  AS 
    SELECT * FROM items_by_id
    WHERE name IS NOT NULL 
      AND id IS NOT NULL
  PRIMARY KEY ((name), id);

Create table items_by_cart:

CREATE TABLE items_by_cart (
  cart_id UUID,
  timestamp TIMESTAMP,
  item_id TEXT,
  item_name TEXT,
  item_description TEXT,
  item_price DECIMAL,
  quantity INT,
  cart_subtotal DECIMAL STATIC,
  PRIMARY KEY ((cart_id),timestamp,item_id)
) WITH CLUSTERING ORDER BY (timestamp DESC, item_id ASC);

Populate tables

Execute the CQL script to insert sample data:

SOURCE '~/shopping_cart_data.cql'

Retrieve all rows from table carts_by_user:

SELECT user_id, cart_name, 
       cart_id, cart_is_active
FROM carts_by_user;

Retrieve all rows from table items_by_id:

SELECT * FROM items_by_id;

Retrieve all rows from materialized view items_by_name:

SELECT * FROM items_by_name;

Retrieve all rows from table items_by_cart:

SELECT cart_id, timestamp, item_id 
FROM items_by_cart; 

SELECT cart_id, item_id, item_price, 
       quantity, cart_subtotal 
FROM items_by_cart;

Design query Q1

Find id and name of an active shopping cart that belongs to user jen:

Solution 1 (preferred)

-- Retrieve all carts for jen
-- and scan the result set
-- within an application
-- to find an active cart.
SELECT user_id, cart_name, 
       cart_id, cart_is_active
FROM carts_by_user
WHERE user_id = 'jen';

Solution 2

-- Retrieve all carts for jen
-- and scan the result set
-- within Cassandra
-- to find an active cart.
-- Note that this is a rare case of
-- scanning within a small partition
-- when ALLOW FILTERING 
-- might be acceptable. 
SELECT user_id, cart_name, 
       cart_id, cart_is_active
FROM carts_by_user
WHERE user_id = 'jen'
  AND cart_is_active = true 
ALLOW FILTERING;

Design query Q4

Find ids and names of all shopping carts that belong to user jen; order by cart name (asc):

Solution

SELECT user_id, cart_name, 
       cart_id, cart_is_active
FROM carts_by_user
WHERE user_id = 'jen';

Design update U2

Save an active shopping cart with name My Birthday and id 4e66baf8-f3ad-4c3b-9151-52be4574f2de, and designate a different cart with name Gifts for Mom and id 19925cc1-4f8b-4a44-b893-2a49a8434fc8 to be a new active shopping cart for user jen:

Solution

BEGIN BATCH
  UPDATE carts_by_user 
  SET cart_is_active = false
  WHERE user_id = 'jen'
    AND cart_name = 'My Birthday'
    AND cart_id = 4e66baf8-f3ad-4c3b-9151-52be4574f2de
  IF cart_is_active = true;
  UPDATE carts_by_user 
  SET cart_is_active = true
  WHERE user_id = 'jen'
    AND cart_name = 'Gifts for Mom'
    AND cart_id = 19925cc1-4f8b-4a44-b893-2a49a8434fc8;
APPLY BATCH;

SELECT user_id, cart_name, 
       cart_id, cart_is_active
FROM carts_by_user
WHERE user_id = 'jen';

Design query Q2

Find all information about an item with id Box2:

Solution

SELECT * 
FROM items_by_id
WHERE id = 'Box2';

Design query Q3

Find all information about items with name Chocolate Cake:

Solution

SELECT * 
FROM items_by_name
WHERE name = 'Chocolate Cake';

Design query Q5

Find all items and their subtotal for a cart with id 19925cc1-4f8b-4a44-b893-2a49a8434fc8; order items by timestamp (desc):

Solution

SELECT timestamp, item_id, item_price, 
       quantity, cart_subtotal 
FROM items_by_cart
WHERE cart_id = 19925cc1-4f8b-4a44-b893-2a49a8434fc8;

Design update U1

Add item Box2 into active cart 19925cc1-4f8b-4a44-b893-2a49a8434fc8 and update the cart subtotal to 111.50:

Solution

BEGIN BATCH
  INSERT INTO items_by_cart (
    cart_id,
    timestamp,
    item_id,
    item_name,
    item_description,
    item_price,
    quantity)
  VALUES (
    19925cc1-4f8b-4a44-b893-2a49a8434fc8,
    TOTIMESTAMP(NOW()),
    'Box2',
    'Chocolates',
    '25 gourmet chocolates from our collection',
    60.00,
    1);
  UPDATE items_by_cart 
  SET cart_subtotal = 111.50
  WHERE cart_id = 19925cc1-4f8b-4a44-b893-2a49a8434fc8
  IF cart_subtotal = 51.50;
APPLY BATCH;

SELECT timestamp, item_id, item_price, 
       quantity, cart_subtotal 
FROM items_by_cart
WHERE cart_id = 19925cc1-4f8b-4a44-b893-2a49a8434fc8;

cd <directory>	Change directory
ls	List directory
echo 'contents' > <file>	Write contents to a file
cat <file>	Output contents of file

i	In Command Mode	Change into Insert Mode, you can now insert and edit text in the file
esc	In Insert Mode	Change into Command Mode, you can now execute commands
:wq	In Command Mode	Save and Exit

Welcome!

Congratulations!

You've completed the scenario!

Scenario Rating

Steps