Cassandra: Design Data Model for User, Roles and Permissions

Question

I have a requirement of designing Data Model in cassandra for User, Role, Organization and permissions.

1. Each Organization can have Users and Roles
2. Each User can belong to number of Roles
3. Each Role can have number of Users and Permissions.

So, based on the above design requirement, following will be my queries:

1. For an Organization get all Users / Roles
2. For a User get all Roles
3. For a Role get all Users / Permissions

Can anybody please help me in designing the data model for the above requirement.

Tooley

Answer 1

Cassandra does not have foreign key relationships like a relational database does (see here and here), which means you cannot join multiple column families to satisfy a given query request.
Here are the two possible solutions:

---

Solution 1: denormalize organizatios and users.

Simply create a Users table (i.e., a denormalized table) similar to the following:

create table Users (
        ... organization ascii,
        ... uid int primary key,
        ... role set<ascii>,
        ... permission set<ascii>);

and create an index for organization to allow querying on non-key column:

create index demo_users_organization on users (organization);

This can satisfy the first two of your requirements:

Query 1 --- For an organization, get all users / roles:

cqlsh:demo> select * from users where organization='stack overflow';

 uid | organization   | permission                               | role
-----+----------------+------------------------------------------+-----------------------------
   1 | stack overflow |                     {down-vote, up-vote} |                  {end user}
   2 | stack overflow |         {close-vote, down-vote, up-vote} |       {end user, moderator}
   3 | stack overflow | {close-vote, down-vote, reboot, up-vote} | {end user, moderator, root}

Query 2 --- For a user get all roles

cqlsh:demo> select role from users where uid = 2;

 role
-----------------------
 {end user, moderator}

However, since index on collections are not yet supported, this denormalized table cannot handle your third requirement:

cqlsh:demo> create index demo_users_role on users (role);
Bad Request: Indexes on collections are no yet supported

---

Solution 2: denormalize organizations, users, and roles.

One work-around for Solution 1 is to further denormalize the user and role where each (user, role) pair has a row in the table:

cqlsh:demo> create table RoleUsers (
    ... uid int,
    ... organization ascii,
    ... role ascii,
    ... permission set<ascii>,
    ... primary key(uid, role));

and again, create an index for organization.

Here are the example rows:

 uid | role      | organization   | permission
-----+-----------+----------------+------------------------------------------
   1 |  end user | stack overflow |                     {down-vote, up-vote}
   2 |  end user | stack overflow |         {close-vote, down-vote, up-vote}
   2 | moderator | stack overflow |         {close-vote, down-vote, up-vote}
   3 |  end user | stack overflow | {close-vote, down-vote, reboot, up-vote}
   3 | moderator | stack overflow | {close-vote, down-vote, reboot, up-vote}
   3 |      root | stack overflow | {close-vote, down-vote, reboot, up-vote}

Now, you are able to perform the third query.

Query 3 --- For a Role get all Users / Permissions:

cqlsh:demo> select uid from roleusers where role='moderator' allow filtering;

 uid | permission
-----+------------------------------------------
   2 |         {close-vote, down-vote, up-vote}
   3 | {close-vote, down-vote, reboot, up-vote}

Hawger