Cassandra data model design approach

Question

I am new Cassandra. please help me to understand how to define best data model in Cassandra.

Model 1:

CREATE TABLE users (
    id uuid PRIMARY KEY,
    username text,
    email text,
    age int
)

CREATE TABLE users_by_username (
    username text PRIMARY KEY,
    id uuid
)

CREATE TABLE users_by_email (
    email text PRIMARY KEY,
    id uuid
)

Advantages

: 1.No duplicate records. 2. Update/Delete only once , But to find user details need one select
query.

Dis Advantages

    1.  To get user records, Need to select in 2 tables (  users_by_username   
         or users_by_email and users )

Model 2:

 CREATE TABLE users_by_username (
    id uuid PRIMARY KEY,
    username text,
    email text,
    age int
)

  CREATE TABLE users_by_email (
    id uuid PRIMARY KEY,
    username text,
    email text,
    age int
)

Advantages:

  1.  To get user records, only once select query.

Dis Advantages:

    1.Duplicate records in two tables.
    2. Update/Delete needs to performed in two tables.

Please suggest me which model will be good ?

score 0 · Answer 1 · answered Sep 16 '16 at 06:02

0

The best way to model tables (column families) is to define first how are you going to access the information: - are you going to query by uuid, email, username? your examples would work fine only if you will be looking by uuid (primary key)

answered Sep 16 '16 at 06:02

Carlos Monroy Nieblas

2,225
2
16
27

query by email for one use case and query by username for another use case – Gnana Sep 16 '16 at 14:53
In that case, you will need 2 tables: ` CREATE TABLE users_by_username ( id uuid, username text, email text, age int, PRIMARY KEY (id, username) )` `CREATE TABLE users_by_email ( id uuid, username text, email text, age int, PRIMARY KEY (id, email) )` When doing inserts, updates or deletes you would need to use atomic [updates](https://docs.datastax.com/en/cql/3.1/cql/cql_reference/batch_r.html) `BEGIN UNLOGGED BATCH INSERT INTO users_by_username VALUES (...); INSERT INTO users_by_email VALUES (...); APPLY BATCH;` – Carlos Monroy Nieblas Sep 16 '16 at 21:20
Thanks. it will be helpful – Gnana Sep 17 '16 at 06:27

Cassandra data model design approach

1 Answers1