Many-to-Many Modeling in Cassandra 2 with CQL3

Question

What is the canonical way to model many-to-many relationships with CQL3? Let them say that I need tables

CREATE TABLE actor ( id text PRIMARY KEY, given text, surname text, ) CREATE TABLE fan ( id text PRIMARY KEY, given text, surname text, ) 

and I would like to simulate the fact that an actor can have many fans, and many fans can like each fan.

The first idea that came to me was to use sets , as in the following (and vice versa for fans):

 CREATE TABLE actor ( id text PRIMARY KEY, given text, surname text, fans set<text> ) <similarly for fan> 

but it seems they are intended for small sets, and I see no way to check if the fan is connected to the actor without a full load.

The second choice I found is to make two mapping tables, each for each direction of the relationship:

 CREATE TABLE actor_fan ( text actor, text fan, PRIMARY KEY(actor,fan) ); <similarly for fan_actor> 

This will give me the opportunity to get a list of fans of the actor, and check whether a particular person is a fan of this actor? There is a lot of documentation about Cassandra, but it is often associated with older versions, and there seem to be many differences between releases.

Answer 1

The right way to do this in Kassandra is to denormalize the data into 2 tables. You do not have to worry about writing twice as soon as on each table, as Cassandra is designed to quickly record notes to support such a model.

Take a look at this data modeling tutorial to help you understand these things:

Data Modeling Tutorials

Also I see that you mentioned a lot. As a note, and although this is not the answer to your questions, you may be aware of some new features, such as: http://www.datastax.com/dev/blog/cql-in-2-1

Answer 2

A way to achieve this is to denormalize the data creating actors_by_fans and a fans_by_actors . You can also use kits, but you have limitations that you already mentioned.

HTH, Carlo