CODEWITHSUNDEEP
cassandradatastax
Ajmani
Ajmani

Reputation: 43

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
     - Kormangala
   - 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.

Upvotes: 1

Views: 60

Answers (1)

Saifallah KETBI
Saifallah KETBI

Reputation: 303

Simple solution : 

CREATE TABLE state 
 (id int,
 name text,
 child_count int,
 PRIMARY KEY (id));

 CREATE TABLE hierarchy  
 (parent_id int,
 child_id int,
 PRIMARY KEY (id, child_id));

child_id must be a clustering column.

Less obvious solution with static columns : 

 CREATE TABLE state 
 (id int,
 name text static,
 child_count static,
 child_id int,
 PRIMARY KEY (id, child_id));

For more information about static columns : https://docs.datastax.com/en/dse/6.0/cql/cql/cql_using/refStaticCol.html

I hope this helps

Upvotes: 1

Related Questions