Practice Data Modeling

CREATE TABLE users ( username text PRIMARY KEY, user_id uuid, display_name text, bio text, created_at timestamp, follower_count counter, following_count counter );

CREATE TABLE tweets_by_user ( username text, tweet_id timeuuid, content text, likes_count int, retweets_count int, PRIMARY KEY (username, tweet_id) ) WITH CLUSTERING ORDER BY (tweet_id DESC);

CREATE TABLE timeline ( username text, -- The viewer tweet_id timeuuid, author_username text, -- Who posted it content text, posted_at timestamp, PRIMARY KEY (username, tweet_id) ) WITH CLUSTERING ORDER BY (tweet_id DESC);

CREATE TABLE following ( follower_username text, following_username text, followed_at timestamp, PRIMARY KEY (follower_username, following_username) );

CREATE TABLE products ( product_id uuid PRIMARY KEY, name text, description text, price decimal, stock_quantity int, categories set<text>, images list<text> );

CREATE TABLE products_by_category ( category text, product_id uuid, name text, price decimal, image_url text, PRIMARY KEY (category, product_id) );

CREATE TABLE orders_by_user ( user_id uuid, order_id timeuuid, order_date timestamp, total_amount decimal, status text, PRIMARY KEY (user_id, order_id) ) WITH CLUSTERING ORDER BY (order_id DESC);

CREATE TABLE order_items ( order_id timeuuid PRIMARY KEY, user_id uuid, order_date timestamp, items list<frozen<order_item>>, subtotal decimal, tax decimal, shipping decimal, total decimal ); -- UDT for order items CREATE TYPE order_item ( product_id uuid, product_name text, price decimal, quantity int );

CREATE TABLE shopping_carts ( user_id uuid, product_id uuid, quantity int, added_at timestamp, PRIMARY KEY (user_id, product_id) );

CREATE TABLE sensor_readings ( sensor_id text, bucket text, -- 'YYYY-MM-DD' for daily bucket reading_time timestamp, temperature decimal, humidity decimal, battery_level int, PRIMARY KEY ((sensor_id, bucket), reading_time) ) WITH CLUSTERING ORDER BY (reading_time DESC) AND compaction = { 'class': 'TimeWindowCompactionStrategy', 'compaction_window_size': '24', 'compaction_window_unit': 'HOURS' } AND default_time_to_live = 2592000; -- 30 days

CREATE TABLE sensors ( sensor_id text PRIMARY KEY, location text, building text, floor int, room text, install_date timestamp, last_reading timestamp, status text );

CREATE TABLE hourly_stats ( sensor_id text, hour timestamp, -- Truncated to hour avg_temp decimal, min_temp decimal, max_temp decimal, reading_count int, PRIMARY KEY (sensor_id, hour) ) WITH CLUSTERING ORDER BY (hour DESC) AND default_time_to_live = 31536000; -- 1 year

CREATE TABLE sensors_by_location ( location text, sensor_id text, building text, floor int, room text, PRIMARY KEY (location, sensor_id) );

CREATE TABLE players ( player_id uuid PRIMARY KEY, username text, level int, total_score bigint, games_played int, wins int, losses int, created_at timestamp );

CREATE TABLE leaderboard_global ( board_type text, -- 'daily', 'weekly', 'alltime' score bigint, player_id uuid, username text, updated_at timestamp, PRIMARY KEY (board_type, score, player_id) ) WITH CLUSTERING ORDER BY (score DESC, player_id ASC);

CREATE TABLE friend_leaderboard ( player_id uuid, score bigint, friend_id uuid, friend_username text, PRIMARY KEY (player_id, score, friend_id) ) WITH CLUSTERING ORDER BY (score DESC);

CREATE TABLE match_history ( player_id uuid, match_id timeuuid, match_date timestamp, opponent_id uuid, opponent_username text, score int, result text, -- 'win', 'loss', 'draw' PRIMARY KEY (player_id, match_id) ) WITH CLUSTERING ORDER BY (match_id DESC);

-- After match completion: BEGIN BATCH -- Update player stats UPDATE players SET total_score = total_score + 100, games_played = games_played + 1, wins = wins + 1 WHERE player_id = ?; -- Update leaderboards (delete old, insert new) DELETE FROM leaderboard_global WHERE board_type = 'alltime' AND score = ? AND player_id = ?; INSERT INTO leaderboard_global (board_type, score, player_id, username) VALUES ('alltime', ?, ?, ?); APPLY BATCH;

CREATE TABLE messages ( conversation_id uuid, -- Deterministic: min(user1,user2)+max(user1,user2) message_id timeuuid, sender_id uuid, content text, status text, -- 'sent', 'delivered', 'read' edited boolean, deleted boolean, PRIMARY KEY (conversation_id, message_id) ) WITH CLUSTERING ORDER BY (message_id DESC);

CREATE TABLE user_conversations ( user_id uuid, conversation_id uuid, other_user_id uuid, other_username text, last_message text, last_message_time timestamp, unread_count int, PRIMARY KEY (user_id, last_message_time, conversation_id) ) WITH CLUSTERING ORDER BY (last_message_time DESC);

CREATE TABLE unread_messages ( user_id uuid, conversation_id uuid, message_id timeuuid, sender_id uuid, content text, received_at timestamp, PRIMARY KEY (user_id, conversation_id, message_id) );

CREATE TABLE group_messages ( group_id uuid, message_id timeuuid, sender_id uuid, sender_username text, content text, PRIMARY KEY (group_id, message_id) ) WITH CLUSTERING ORDER BY (message_id DESC);

-- When message is read: BEGIN BATCH -- Update message status UPDATE messages SET status = 'read' WHERE conversation_id = ? AND message_id = ?; -- Remove from unread DELETE FROM unread_messages WHERE user_id = ? AND conversation_id = ? AND message_id = ?; -- Update conversation unread count UPDATE user_conversations SET unread_count = 0 WHERE user_id = ? AND last_message_time = ? AND conversation_id = ?; APPLY BATCH;