title: How to implement Twitter retweet action in my database
body: <p>I am implementing web application similar to Twitter. I need to implement 'retweet' action, and <strong>one tweet can by retweeted by one person multiple times</strong>.</p>
  
  <p>I have a basic 'tweets' table that have columns for:</p>
  
  <blockquote>
    <p><strong>Tweets:</strong> tweet_id | tweet_text | tweet_date_created | tweet_user_id</p>
  </blockquote>
  
  <p>(where <code>tweet_id</code> is primary key for tweets, <code>tweet_text</code> contains tweet text, <code>tweet_date_created</code> is the DateTime when tweet was created and <code>tweet_user_id</code> is the foreign key to <code>users</code> table and identifies user who has created the tweet)</p>
  
  <p>Now I am wondering how should I implement the retweet action in my database.</p>
  
  <h2>Option 1</h2>
  
  <p>Should I create new join table, which would look like this:</p>
  
  <blockquote>
    <p><strong>Retweets</strong>: tweet_id | user_id | retweet_date_retweeted</p>
  </blockquote>
  
  <p>(Where <code>tweet_id</code> is a foreign key to <code>tweets</code> table, <code>user_id</code> is a foreign key to <code>users</code> table and identifies user who has retweeted the tweet, <code>retweet_date_retweeted</code> is a DateTime which specifies when the retweet was done.)</p>
  
  <p><strong>pros:</strong> There will be no empty columns, when user process reteet, new line in <code>retweets</code> table will be created.</p>
  
  <p><strong>cons:</strong> Querying process will be more difficult, it will need to join two tables and somehow  sort the tweets by two dates (when tweet is not retweet, sort it by tweet_date_created, when tweet is retweet, sort it by retweet_date_retweeted).</p>
  
  <h2>Option 2</h2>
  
  <p>Or should I implement it in the <code>tweets</code> table as <code>parent_id</code>, it will then look like this:</p>
  
  <blockquote>
    <p><strong>Tweets:</strong>  tweet_id | tweet_text | tweet_date_created | tweet_user_id | parent_id</p>
  </blockquote>
  
  <p>(Where all the columns remains the same and <code>parent_id</code> is a foreign key to the same <code>tweets</code> table. When tweet is created, <code>parent_id</code> remains empty. When tweet is retweeted, <code>parent_id</code> contains origin tweet id, <code>tweet_user_id</code> contains user which processed the retweet action, <code>tweet_date_created</code> contains the DateTime when retweet was done, and <code>tweet_text</code> remains empty - becouse <strong>we will not let users change the original tweet when retweeting</strong>.)</p>
  
  <p><strong>pros:</strong> Querying process is much more elegant, as I do not have to join two tables.</p>
  
  <p><strong>cons:</strong> There will be empty cells every time tweet is retweeted. So if I have 1 000 tweets in my database and every of them is retweeted for 5 times, there will be 5 000 lines in my <code>tweets</code> table.</p>
  
  <hr/>
  
  <p>Which is the most efficient way? Is it better to have empty cells or to have querying process more clean?</p>