CODEWITHSUNDEEP
cassandradata-modelingdenormalization
vrnvlk
vrnvlk

Reputation: 21

How do I model data containing movie titles to retrieve a list of movies by genre?

I have a question regarding data modeling in cassandra. I want to create a cassandra database with movies, actors, ratings etc. I already defined what my tables should look like and I want to create tables like movies_by_genre, movies_by_year, actors_by_movie, movies_by_actor and so on. My movie data looks like this for example:

ID title year genres
1 Top Gun: Maverick 2022 action, drama, adventure
2 Black Panther: Wakanda Forever 2019 action, sci-fi, superhero
3 Thor: Love and Thunder 2015 comedy, action, superhero, sci-fi
4 The Hobbit 2012 fantasy, roman
5 Mary Poppins 1964 children, musical

So when I want to create the table movies_by_genre I want the genre as partition key and for genre "Action" an output like this for example:

genre title year
action Top Gun: Maverick 2022
action Black Panther: Wakanda Forever 2019
action Thor: Love and Thunder 2015

So the genre is part of a list as you can see in the first table so every movie can have several genres. How do I have to define my data input for my tables to get the result I require? Do I have to make a single dataset for every genre a movie belongs to?

I'm glad if you can help me!

Upvotes: 1

Views: 139

Answers (1)

Erick Ramirez
Erick Ramirez

Reputation: 16373

If your application needs to retrieve movies by genre then you should design a table that is (1) partitioned by genre and (2) rows clustered by the movie title.

For example, here's a simple example table that is designed for that specific app query:

CREATE TABLE movies_by_genre (
    genre text,
    title text,
    release_year int,
    duration int,
    director text,
    ...
    PRIMARY KEY (genre, title)
)

With this PRIMARY KEY (genre, title) definition, each genre will have one or more rows of movies.

Here's an example query which retrieves 5 action films:

SELECT genre, title FROM movies_by_genre
  WHERE genre = 'action'
  LIMIT 5

And a sample output:

 genre  | title
--------|---------------------------------------------
 action | Black Panther: Wakanda Forever
 action | Doctor Strange in the Multiverse of Madness
 action | The Batman
 action | Thor: Love and Thunder
 action | Top Gun: Maverick

Upvotes: 2

Related Questions