I have these two tables:
create table users (
id UUID,
email ascii,
created_at timeuuid,
primary key(id, email)
);
create table groups (
id UUID,
name ascii,
created_at timeuuid,
primary key(id, name)
);
A user can be in multiple groups, a group can obviously have multiple users.
So I've two ways to maintain a many-to-many relationship (taken from here), one is:
CREATE TABLE user_group (
user UUID,
group UUID,
PRIMARY KEY (user, group)
)
Another one is (using sets):
CREATE TABLE user_jn_group (
user UUID PRIMARY KEY,
groups set<UUID>
)
CREATE TABLE group_jn_user (
group UUID PRIMARY KEY,
users set<UUID>
)
I'm using Cassandra 3.9.0. I know both approaches have their own advantages, disadvantages. I want the least duplicity, but also I have an equal weight to read/write speed. Also, is there any more hidden cost behind any of both approaches?