Carnatic cassandra

Let's talk a bit about NoSQL with carnatic music. ;)

What if one has to represent the melakarta chart as a data model using an expandable schema and improve it for other use-cases? Here we go...

Melakartas define fundamental scales that are used in carnatic music (a classical musical form from southern part of India).

I'll start from defining a schema like this (An image below for our reference)

My primary key is going to be a combination of name of the chakra (the segments of the circle - indu, netra, agni and all) and the melakarta_id.

create table melakarta(chakra text,melakarta_id int,melakarta_name text,notation list<text>,shudhamadhyama boolean,primary key (chakra,melakarta_id));

Get some records in place. 

As a lighter side note, if you observe the names of melakartas like one of my colleagues did, carnatic system gives a lot of prominence to women.

insert into melakarta (chakra,melakarta_id,melakarta_name,notation,shudhamadhyama) values ('veda',19,'Jhamkaradhwani',['S','R2','G2','M1','P','D1','N1','S'], true);

insert into melakarta (chakra,melakarta_id,melakarta_name,notation,shudhamadhyama) values ('veda',20,'Natabhairavi',['S','R2','G2','M1','P','D1','N2','S'], true);

insert into melakarta (chakra,melakarta_id,melakarta_name,notation,shudhamadhyama) values ('veda',21,'Keeravani',['S','R2','G2','M1','P','D1','N3','S'], true);

insert into melakarta (chakra,melakarta_id,melakarta_name,notation,shudhamadhyama) values ('veda',22,'Karaharapriya',['S','R2','G2','M1','P','D2','N2','S'], true);

insert into melakarta (chakra,melakarta_id,melakarta_name,notation,shudhamadhyama) values ('veda',23,'Gowrimanohari',['S','R2','G2','M1','P','D2','N3','S'], true);

insert into melakarta (chakra,melakarta_id,melakarta_name,notation,shudhamadhyama) values ('veda',24,'Varunapriya',['S','R2','G2','M1','P','D3','N3','S'], true);

In such a schema, each super row has 6 column families - as cassandra calls it - each with 6 melakartas.

How do we add ragas to this schema?

Multiple ragas can be derived from same melakarta. How can we store multiple ragas under each melakarta? 
Thanks to the collections (all your sets, lists and maps) in cassandra. We can always add another column in cassandra which has the set of ragas. I know you already noticed the list of notations. I'm just showing off!  

create table melakarta(chakra text,melakarta_id int,melakarta_name text,notation list<text>,shudhamadhyama boolean,ragas set<text>,
primary key (chakra,melakarta_id));

One thing to note about these column entries is that they insert keys in the sorted order automatically. Easy search, folks, easy!

If one were to design the same schema in an RDBMS world, it should probably look like this

create table melakarta(chakra text,melakarta_id int,melakarta_name text,notation list<text>,shudhamadhyama boolean,primary key (melakarta_id));

create table raga(raga_id int,raga_name text,melakarta_id int,primary key (raga_id));

If you were to pick which melakarta a raga belongs to, you would end up doing 2 lookups. Search table: raga and use the key melakarta_id to fetch the melakarta_name again. 

In a noSQL system, we highly encourage duplication.

create table raga(raga_id int,raga_name text,melakarta_name text,primary key (raga_id));

Make your queries less complex by bringing data to the same data model instead of incorporating FK's. This is a fundamental shift from the world of normalization. 

We thus knock some sense into the unstructured world with a little bit of compromise - duplication.