question

@KARTHIKEYAN RASIPALAYAM DURAIRAJ when modelling your tables, you should always start with the queries. For example if you want to retrieve the country based on the state code, you would partition your table using the state code like this:

CREATE TABLE country_by_state_code (
    state_code text,
    country text,
    PRIMARY KEY (state_code)
)

If you want to retrieve the list of cities based on the state code, your table would look like this:

CREATE TABLE cities_by_state_code (
    state_code text,
    city text,
    PRIMARY KEY ( state_code, city )
)

The filter for your queries would always determine how the table will be partitioned and therefore determine the partition key. Cheers!

As any data modeling exercise in Cassandra, you create models to match the needed query. Your example above mixes country, state and city. To simplify, I'm going to make this a city and state lookup with a 1 to many and many-to-many relationship.

Consider this simplified table:

CREATE TABLE city_by_state (
   state text,
   city text,
   country text
)

What's the primary key?

To look up any city in a state, the query would be:

SELECT state, city, country
FROM city_by_state
WHERE state='CA' AND city='San Francisco'

To support this query, your partition key will be the state(one) and the clustering column will be the city(many).

PRIMARY KEY(state, city)

In order to have a many-to-many lookup, you will need to have a duplicate table with the reversed primary key

CREATE TABLE city_by_state (
   state text,
   city text,
   country text
) PRIMARY KEY(city, state)

This would support the query to look up all states with a similar city name.

Reminder: To insert or update data in these tables, use a batch to make sure they are in sync.