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.
@nehajmani6_184465 The basic premise 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!
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.
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!
5 People are following this question.
