Cassandra modeling with a read/unread status for a message inbox, CQL

Question

I'm trying to find the best data model for a message box application. That messages appear in order in which first the ‘unread’ appear and then as the user scrolls the ‘read’ messages will appear. In both of the categories I want to sort the messages by arrival time. Something like priority inbox in gmail.

The first schema I thought to use is :

CREATE TABLE inbox 
    (userId     uuid,
     messageId  timeuuid,
     data       blob,
     isRead     boolean,
  PRIMARY KEY(userId, isRead, messageId))
  WITH CLUSTERING ORDER BY (isRead ASC, messageId DESC);

So My data is first sorted by the boolean field and then by time. Now I can easily go over first my 'unread' messages and after they all end then I will start reading the 'read' messages.

The problem is that I can't update any message status, since it's a part of the primary key. I can do a delete and then insert in a batch operation, it's also the same row.

Another solution will be :

CREATE TABLE inbox
    (userId     uuid,
     messageId  timeuuid,
     data       blob,
     isRead     boolean,
  PRIMARY KEY((userId, isRead), messageId))
  WITH CLUSTERING ORDER BY (messageId DESC)

Having a row for every status. I gain a very easy access but does that mean that I have to deal with transaction? When reading a message I have to delete it from the ‘unread’ row and insert it to the ‘read’ row, they might be in different partitions.

another version for the partition key can be :

PRIMARY KEY(userId, messageId)

and then I would add a secondary index on isRead. My queries will always be on a certain user and not a group of user.

Any ideas on what is better? Or any other modeling ideas?

What is your cassandra version ? – Guillaume S Nov 18 '15 at 21:09 — Guillaume S, Nov 18 '15 at 21:09

score 0 · Answer 1 · answered Nov 22 '15 at 18:16

You can create a table referencing you messages by id for exemple :

CREATE TABLE inbox 
(inbox_id   uuid,
 userId     uuid,
 messageId  timeuuid,
 data       blob,
 isRead     boolean,

PRIMARY KEY(inbox_id));

This table store you datas and perform update operations.

Create other tables for search like

CREATE TABLE inbox 
(inbox_id   uuid,
 userId     uuid,
 messageId  timeuuid,
 isRead     boolean,
PRIMARY KEY((userId, isRead), messageId))
WITH CLUSTERING ORDER BY (isRead ASC, messageId DESC);

Search desired records in this table and update in both tables.

on the second table when I need to update status I can't use update since the isRead is a part of the partition key. What I could do is to use delete and insert. Which might result in two different nodes. How is this any better than the second solution that I suggested when the isRead is part of the clustering key ? — Ribak, Nov 25 '15 at 13:35

Cassandra modeling with a read/unread status for a message inbox, CQL

1 Answers1