how does the retweet mechanism work on twitter?

Question

I'm creating similar thing to twitter retweet function!

This is my database table for tweets (status messages):

ID || USER_ID || TWEET || RETWEET_ID || RETWEET_NAME || DATE

What I'm trying to find out is, if the logged in user retweets something, when they refresh the page I don't want them to see the retweet button (because obviously they have retweeted it). I'm more concerned with the app logic; it's driving me crazy.

p.s. a user's timeline is queried using a while loop array, so it has to distinguish between a normal tweet, and retweeted tweet, if you get my drift!! :))

EDIT: another thing is if it's a normal tweet, both values RETWEET_ID and RETWEET_NAME are NULL, so that's how I can differentiate between the two!!

Answer 1

I see two options

1) You use similar to what you have now

ID || USER_ID || TWEET_TEXT || RETWEET_ID || DATE

But drop the retweet name. If each tweet id is unique, then your tweet would contain either a tweet_text or a retweet_id.

2) The second option, and better solution would be to have two tables, tweets and retweets.

TWEETS { ID || USER_ID || TWEET_TEXT || DATE }
RETWEETS {ID || USER_ID || RETWEET_ID || DATE }

The second option is cleaner, and will mean you will not have empty columns in your database, but the first option will make your querying easier, as you do not have to join the two tables, or create a view over the table etc.