Sets for membership or individual rows in Cassandra

Author: eof

Originally Sourced from: https://stackoverflow.com/questions/74373660/sets-for-membership-or-individual-rows-in-cassandra

I'm trying to see what would be the best way to model groups in Cassandra. There are basically two options, store members as set or create individual rows for each group member. In CQL this would be:

CREATE TABLE groups (
    group_id uuid,
    member_id text,
    name text,
    PRIMARY KEY ((group_id), member_id)
);

CREATE TABLE groups_by_member (
    member_id text,
    group_id uuid,
    PRIMARY KEY ((member_id), group_id)
);

An alternative using sets:

CREATE TABLE groups (
    group_id text PRIMARY KEY,
    member_ids set<text>,
    name text
);

CREATE TABLE groups_by_member (
    member_id text PRIMARY KEY,
    group_ids set<uuid>
);

The operations would be typical:

  1. Find all members of a group
  2. Find all groups of a member

In both cases memberships would be changed by batched inserts or deletes. Is either one better than the other? Does the situation change if groups can have 100 or 1000 members?

Yet a third option would be to just have one table with each group member as it's own row, but then use materialized views to allow find all the groups that a user belongs to.