Cassandra Hierachy Data Model

Question

I'm newbie design cassandra data model and I need some help to think out the box.

Basically I need a hierarchical table, something pretty standard when talking about Employee.

You have a employee, say Big Boss, that have a list of employee under him.

Something like:

create table employee(id timeuuid, name text, employees list<employee>, primary key(id));

So, is there a way to model a hierarchical model in Cassandra adding the table type itself, or even another approach?

When trying this line above it give me

Bad Request: line 1:61 no viable alternative at input 'employee'

EDITED

I was thinking about 2 possibilities:

1. Add an uuid instead and in my java application find each uuid Employee when bringing up the "boss".

2. Working with Map, where the uuid is the id itself and my text would be the entire Row, then in my java application get the maps, convert each "text" employee into a Employee entity and finally return the whole object;

Answer 1

It really depends on your queries...one particular model would only be good for a set of queries, but not others.

You can store ids, and look them up again at the client side. This means n extra queries for each "query". This may or may not be a problem, as queries that hit a partition are fast. Using a map from id to name is also an option. This means you do extra work and denormalise the names into the map values. That's also valid. A third option is to use a UDT (user defined type). You could then have a list or set or even map. In cassandra 2.1, you could index the map keys/ values as well, allowing for some quite flexible querying.

https://www.datastax.com/documentation/cql/3.1/cql/cql_using/cqlUseUDT.html

One more approach could be to store a person's details as id, static columns for their attributes, and have "children" as columns in wide row format.

This could look like

create table person(
  id int primary key,
  name text static,
  age int static,
  employees map<int, employeeudt>
);

http://www.datastax.com/documentation/cql/3.1/cql/cql_reference/refStaticCol.html

Querying this will give you rows with the static properties repeated, but on disk, it's still held once. You can resolve the rest client side.