Bringing together the Apache Cassandra experts from the community and DataStax.

Want to learn? Have a question? Want to share your expertise? You are in the right place!

Not sure where to begin? Getting Started

 

question

KARTHIKEYAN RASIPALAYAM DURAIRAJ avatar image
KARTHIKEYAN RASIPALAYAM DURAIRAJ asked ·

Data Modeling : One to Many and Many to Many

Source Data : One to Many

Country ID Country Name State Code
100 USA CA
100 USA AZ
100 USA GA
200 IND TN
200 IND AP
200 IND DEL


In this table 1- Many relation between Country to State . How do Data modeling.


Is it correct ? Please confirm.

create table one_to_many_dm6 (

state text,

country text,

city text,

id int,

primary key((state,city),id));


In same manner , How do many to many relationship data modeling . Kindly share the details with example . Thanks in advance.


Regards


Karthikeyan Rasipalayam Durairaj

data modeling
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

pmcfadin avatar image
pmcfadin answered ·

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.


Share
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Erick Ramirez avatar image
Erick Ramirez answered ·

@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!

Share
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.