Hallöchen!
In RDBMS terms, I have a n:m relationship between "users" and
"groups". I need to answer the questions "who's in that group" and
"in which groups is he". In my Cassandra DB, this looks like this:
CREATE TABLE users (
id uuid PRIMARY KEY,
groups_member set<uuid>
groups_admin set<uuid>
groups_pending set<uuid>
);
CREATE TABLE groups (
id uuid PRIMARY KEY,
members set<uuid>
admins set<uuid>
pending set<uuid>
);
But someone suggested to me to express the membership relation by
this:
CREATE TABLE group_status (
group uuid,
user uuid,
status text, /* "member", "admin", "pending" */
PRIMARY KEY ((group, user))
);
CREATE MATERIALIZED VIEW group_status_group AS
SELECT user, status FROM group_status
WHERE user IS NOT NULL AND status IS NOT NULL and group IS NOT NULL
PRIMARY KEY (group, user);
CREATE MATERIALIZED VIEW group_status_user AS
SELECT group, status FROM group_status
WHERE user IS NOT NULL AND status IS NOT NULL and group IS NOT NULL
PRIMARY KEY (user, group);
The answer to "who's in that group" is here "SELECT * FROM
group_status_group WHERE group = <group-id>".
Let's analyse both, and please interrrupt me if I write something
wrong.
Simplicity: Table layout is easier to understand in the first
variant, however, code is simpler with the second variant as you
only need one update instead of a batch with up to six sets. The
second variant is easier to extend to further states.
Consistenty: Eventual consistency can be guaranteed in both cases.
Performance: Read performance is much better for the first variant,
because the second variant has to go through many rows to collect
all non-deleted clustering key values. Write performance is
slightly better for the first variant because one table + two
materialised views is more expensive than two tables.
What would you prefer?
Tschö,
Torsten.
--
Torsten Bronger
