Many-To-Many Cassandra Database -

let have users. users can have access multiple projects. project can allow multiple users.

so model 4 tables. users (by_id), projects (by id), projects_by_user_id , users_by_project_id.

 -----------    ------------    --------------------    -------------------- | users   |    | projects |    | projects_by_user |    | users_by_project | |---------|    |--------- |    |------------------|    |------------------| | id    k |    | id     k |    | user_id        k |    | project_id     k | | name    |    | name     |    | project_id     c |    | user_id        c | -----------    ------------    | project_name   s |    | user_name      s |                                --------------------    -------------------- 

so storing user_name in users_by_project , projet_name in projects_by_user table querying.

the problem have when user updates project_name, of course update projects table. data consistency need update each partition in projects_by_user table.

as far can see, possible querying users users_by_project table , doing update each user.

is there better way without first reading lots of data?

i don't see why need 4 tables. users , projects tables contain of data.

if define tables this:

create table users (     user_id int primary key,     name text,     project_ids list<int> );  create table projects (     project_id int primary key,     name text,     user_ids list<int> ); 

then each user have list of project ids have access to, , each project have list of users have access it.

to add access project 123 user 1 run:

begin batch update users set project_ids = project_ids + [123] user_id=1; update projects set user_ids = user_ids + [1] project_id=123; apply batch; 

to change project name, do:

update projects set name = 'new project name' project_id=123; 

for simplicity showed id fields int's, use uuid's that.