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

nehajmani6_184465 avatar image
nehajmani6_184465 asked ·

How to create a table to maintain a hierarchy structure in Cassandra?

We need a table to maintain a hierarchy to perform all the CRUD operations.


Sample:

- Karnataka

-- Bangalore

--- Kormanagala

-- Hubli

- Madhya Pradesh

-- Gwalior

- Delhi


I thought of the following structure:

Table 1:

State ID(uuid) Name childCount

101 Karnataka 2

102 Bangalore 1

103 Kormangala 0

104 Hubli 0

105 Madhya Pradesh 1

106 Gwalior 0

107 Delhi 0


Table 2:

Parent_Id child_id

101 102

101 104

102 103

105 106


Please suggest a better solution.

data modellinghierarchyhierarchy structure
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.

1 Answer

Erick Ramirez avatar image
Erick Ramirez answered ·

@nehajmani6_184465 The basic premised in data modelling for Cassandra is to start with your queries. It looks like you are trying to model districts, cities and states.

If you want to find out the state based on the city, I would create a table like this:

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

and the query would look like:

SELECT * FROM state_by_city WHERE city = 'Bangalore';

You can do something similar if you wanted to lookup which state or city a district or neighbourhood belongs to. Again, you would model your tables based on the query. Cheers!

2 comments 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.

Thanks @Erick Ramirez, but I need to have following update query as well:

1. Update city or state or district.

2. Delete city or state or district.

0 Likes 0 · ·

You will need to create a table for each of the read queries your application needs to make. When you perform inserts/updates, you will need to update all the tables. For this, we recommend you do it as a BATCH. Cheers!

0 Likes 0 · ·