How to create an efficient Cassandra Data model?

Question

I'm new to Cassandra and trying to create an application. In which I have an entity 'student' consist of 4 columns as given below:

• student_id
• student_name
• dob
• course_name

create table student(student_id uuid, student_name text, dob date, course_name text, PRIMARY KEY(student_id));

I have to search students by course_name. Now according to Cassandra data modeling for searching student by course name I need to create another table as student_by_course_name which consist of two columns:

• course_name
• student_id

where course_name will be the partition key and student_id will be the cluster key as given below:

create table student_by_course_name(course_name text, student_id uuid PRIMARY KEY(course_name, student_id));

The problem arises when a student changes his course. Now I want to update the course name in the student_by_course_name table but it throws an error as the course_name column is a partition key. How to resolve this or pls suggest if i'm using Cassandra data modeling wrongly??

Answer 1

Cassandra isn't the best for deleting data or updating data in-place. I believe that you have to use a batch statement to keep the tables in sync.

You can take two approaches. The first would be to delete the existing student ID/course name combination. This will create a tombstone but if it doesn't happen often, it won't be a big deal. The second option would be to use the original table and to create a secondary index on course name. This will allow both for the course name to be updated and queried by but may not preform well over time.

Answer 2

The best way is indeed as Alex suggested. Delete and then update.

There are a couple of problems than you might need to be aware.

1. If your course have a LOT of students, it will generate big partitions (for this specific case might not be a issue)
2. Deleting entries will cause tombstones, and as such you should be prepared to handle them (Ex: Use low GC_GRACE, if you think a lot will be generated set unchecked_tombstones in the table)

Answer 3

In this case you have to delete the old entry first and then add a new entry to student_by_course_name with the new course.

Your model looks good