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

Question

question

nehajmani6_184465 asked · Jan 16 at 08:06 AM

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 modelling hierarchy hierarchy structure

10 |1000 characters needed characters left characters exceeded

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

Answer 1 · 2020-01-16T08:38:25Z

Erick Ramirez answered · Jan 16 at 08:38 AM

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

10 |1000 characters needed characters left characters exceeded

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

nehajmani6_184465 · Jan 16 at 09:06 AM

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 · ·

Erick Ramirez ♦♦ nehajmani6_184465 · Jan 16 at 09:29 AM

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 · ·

question