Need schema suggestion in cassandra

Question

Here is my tables :

create table user
(
userId uuid primary key,
name text
)

create table comment_by_post
(
postId uuid,
cmntId timeuuid,
cmntTxt text,
cmntBy uuid,
primary key (postId, cmntId)
)

create table post_likes
(
postId uuid,
userId uuid,
primary key (postId, userId)
)

Here i have few question

1. How will I know that a user has commented on a post from comment_by_post table ?

2. How to do order post comments.

3. Ordering post likes in post_likes table

4. If a user is deleted it should remove all entries of given userid from all table.

To run these query what changes I will have to make in schema?

Answer 1

In Cassandra, usually you would need one table per query. So look at your select statement and start creating tables.

1. How will I know that a user has commented on a post from comment_by_post table ?

create table comment_by_post_user
(
    postId uuid,
    userId uuid,
    cmntId timeuuid,
    cmntTxt text,
    cmntBy uuid,
    primary key (postId, userId,commentId)
    )

    select * from comment_by_post_user where postId = <> and userId = <>;

2. How to do order post comments.

create table comment_by_post
(
    postId uuid,
    userId uuid,
    cmntId timeuuid,
    cmntTxt text,
    cmntBy uuid,
    primary key (postId,commentId)
    ) WITH CLUSTERING ORDER BY (commentId) desc;

    select * from comment_by_post where postId = in_postId limit 10;

This will give you latest 10 comments for that post.

3. Ordering post likes in post_likes table

create table post_likes
(
postId uuid,
liked_at timstamp,
userId uuid,
primary key (postId, liked_at)
) WITH CLUSTERING ORDER BY (liked_at) desc;

select * from post_likes limit 10;

4. If a user is deleted it should remove all entries of given userid from all table.

This is not directly possible. To delete from a table you need to know the entire PRIMARY KEY.

One solution is to create another table with userId as PRIMARY KEY and rest of the PRIMARY KEYs of other tables as columns of this table .

create table user
(
userId uuid primary key,
postId uuid,
cmntId timeuuid
)