title: What is the optimal way to model one-to-many relationships in Cassandra?
body: <p>Say I want to design a system where users can create posts, where each post belongs to one user but a user may have multiple posts. Also assume I want to support finding all posts given a userID in addition to simply finding a post by postId. I also want to store user-specific account details like the date of account creation.</p>
  
  <p>One way of modeling this would be as follows:</p>
  
  <pre><code>CREATE TABLE user (
     userId int,
     name varchar,
     userDetail1,
     userDetail2,
     ...,
     PRIMARY KEY(userId)
  );
  
  CREATE TABLE post (
     postId int,
     postDetail1,
     postDetail2,
     ...,
     userId int,
     PRIMARY KEY(postId)
  );
  </code></pre>
  
  <p>From what I've read, this is supposedly not optimal as querying for posts made by a specific user becomes memory inefficient. Is this correct? And is the reason that Cassandra would not support indexing the post table on userId? </p>
  
  <p>So would the ideal solution be as follows?</p>
  
  <pre><code>CREATE TABLE user (
     userId int,
     name varchar,
     userDetail1,
     userDetail2,
     ...,
     PRIMARY KEY(userId)
  );
  
  CREATE TABLE post (
     postId int,
     postDetail1,
     postDetail2,
     ...,
     userId int,
     PRIMARY KEY(postId)
  );
  
  CREATE TABLE user_to_post (
     userId int,
     postId int,
     userDetail1,
     userDetail2,
     ...,
     postDetail1,
     postDetail2,
     ...,
     PRIMARY KEY(userId, postId)
  );
  </code></pre>
  
  <p>Using a composite key, querying for posts for a specific user is much more efficient. But with this design, would having a table for posts specifically be redundant? Again, in this design I want lookups for posts made by a specific user, and also would like to quickly link to a specific user given a post. I have done a lot of reading but am very confused as how to exactly design a one-to-many relationship in Cassandra. </p>