title: Cassandra data model for simple messaging app
body: <p>I am trying to learn Cassandra and always find the best way is to start with creating a very simple and small application. Hence I am creating a basic messaging application which will use Cassandra as the back-end. I would like to do the following:</p>
  
  <ul>
  <li>User will create an account with a username, email, and password. The
  email and the password can be changed at anytime.</li>
  <li>The user can add another user as their contact. The user would add a
  contact by searching their username or email. The contacts don't need
  to be mutual meaning if I add a user they are my contact, I don't
  need to wait for them to accept/approve anything like in Facebook.</li>
  <li>A message is sent from one user to another user. The sender needs to
  be able to see the messages they sent (ordered by time) and the
  messages which were sent to them (ordered by time). When a user opens
  the app I need to check the database for any new messages for that
  user. I also need to mark if the message has been read.</li>
  </ul>
  
  <p>As I come from the world of relational databases my relational database would look something like this:</p>
  
  <pre><code>UsersTable
      username (text)
      email (text)
      password (text)
      time_created (timestamp)
      last_loggedIn (timestamp)
  ------------------------------------------------ 
  ContactsTable
      user_i_added (text)
      user_added_me (text)
  ------------------------------------------------     
  MessagesTable
      from_user (text)
      to_user (text)
      msg_body (text)
      metadata (text)
      has_been_read (boolean)
      message_sent_time (timestamp)
  </code></pre>
  
  <p>Reading through a couple of Cassandra textbooks I have a thought of how to model the database. My main concern is to model the database in a very efficient manner. Hence I am trying to avoid things such as secondary indexes etc. This is my model so far:</p>
  
  <pre><code>CREATE TABLE users_by_username (
      username text PRIMARY KEY,
      email text,
      password text
      timeCreated timestamp
      last_loggedin timestamp
  )
  
  CREATE TABLE users_by_email (
      email text PRIMARY KEY,
      username text,
      password text
      timeCreated timestamp
      last_loggedin timestamp
  )
  </code></pre>
  
  <p>To spread data evenly and to read a minimal amount of partitions (hopefully just one) I can lookup a user based on their username or email quickly. The downside of this is obviously I am doubling my data, but the cost of storage is quite cheap so I find it to be a good trade off instead of using secondary indexes. Last logged in will also need to be written in twice but Cassandra is efficent at writes so I believe this is a good tradeoff as well.</p>
  
  <p>For the contacts I can't think of any other way to model this so I modelled it very similar to how I would in a relational database. This is quite a denormalized design I beleive which should be good for performance according to the books I have read?</p>
  
  <pre><code>CREATE TABLE "user_follows" (
    follower_username text,
    followed_username text,
    timeCreated timestamp, 
    PRIMARY KEY ("follower_username", "followed_username")
  );
  
  CREATE TABLE "user_followedBy" (
  
    followed_username text,
    follower_username text,
    timeCreated timestamp,
    PRIMARY KEY ("followed_username", "follower_username")
  );
  </code></pre>
  
  <p>I am stuck on how to create this next part. For messaging I was thinking of this table as it created wide rows which enables ordering of the messages.
  I need messaging to answer two questions. It first needs to be able to show the user all the messages they have and also be able to show the user
  the messages which are new and are unread. This is a basic model, but am unsure how to make it more efficent?</p>
  
  <pre><code>CREATE TABLE messages (
      message_id uuid,
      from_user text,
      to_user text,
      body text,
      hasRead boolean,
      timeCreated timeuuid,
      PRIMARY KEY ((to_user), timeCreated )
  ) WITH CLUSTERING ORDER BY (timeCreated ASC);
  </code></pre>
  
  <p>I was also looking at using things such as STATIC columns to 'glue' together the user and messages, as well as SETS to store contact relationships, but from my narrow understanding so far the way I presented is more efficient. I ask if there are any ideas to improve this model's efficiency, if there are better practices do the things I am trying to do, or if there are any hidden problems I can face with this design? </p>
  
  <p>In conclusion, I am trying to model around the queries. If I were using relation databases these would be essentially the queries I am looking to answer:</p>
  
  <pre><code>To Login:
  SELECT * FROM USERS WHERE (USERNAME = [MY_USERNAME] OR EMAIL = [MY_EMAIL]) AND PASSWORD = [MY_PASSWORD];
  ------------------------------------------------------------------------------------------------------------------------
  Update user info:
  UPDATE USERS (password) SET password = [NEW_PASSWORD] where username = [MY_USERNAME];
  UPDATE USERS (email) SET password = [NEW_PASSWORD ] where username = [MY_USERNAME];
  ------------------------------------------------------------------------------------------------------------------------ 
  To Add contact (If by username):
  INSERT INTO followings(following,follower)  VALUES([USERNAME_I_WANT_TO_FOLLOW],[MY_USERNAME]);
  ------------------------------------------------------------------------------------------------------------------------
  To Add contact (If by email):
  SELECT username FROM users where email = [CONTACTS_EMAIL];
      Then application layer sends over another query with the username:
  INSERT INTO followings(following,follower)  VALUES([USERNAME_I_WANT_TO_FOLLOW],[MY_USERNAME]);
  ------------------------------------------------------------------------------------------------------------------------
  To View contacts:
  SELECT following FROM USERS WHERE follower = [MY_USERNAME];
  ------------------------------------------------------------------------------------------------------------------------
  To Send Message:,
  INSERT INTO MESSAGES (MSG_ID, FROM, TO, MSG, IS_MSG_NEW) VALUES (uuid, [FROM_USERNAME], [TO_USERNAME], 'MY MSG', true);
  ------------------------------------------------------------------------------------------------------------------------
  To View All Messages (Some pagination type of technique where shows me the 10 recent messages, yet shows which ones are unread):
  SELECT * FROM MESSAGES WHERE TO = [MY_USERNAME] LIMIT 10;
  ------------------------------------------------------------------------------------------------------------------------
  Once Message is read:
  UPDATE MESSAGES SET IS_MSG_NEW = false WHERE TO = [MY_USERNAME] AND MSG_ID = [MSG_ID];
  </code></pre>
  
  <p>Cheers</p>