Database Design - Twitter - PHP

Rolling my own twitter style service with开发者_Python百科in another product using php, and it just hit me if people retwit something it always shows the first posting user twit within retwiter's feed as if the other person had twitter it on their page.

That got me thinking how to handle this within the database and php. If users can twits, retwits, and replays does it sound logical to just setup a database table for each event and then building an object based result by checking each one?

Just wanted to get some others input before sinking the old ship. ;p

I think Twitter just inserts the original tweeter's username before the retweet. It would be very inefficient to always check if anyone tweeted a particular text before.

You could, I suppose, have a database column called "retweeted_id" which stores the tweet_id of the tweet that was originally posted. So if your tweet is ID# 46, and I retweet it, then my Tweet would be #47 with a retweet_id of 46. That way you can always tell directly from the database where that tweet came from. Additionally, you could follow the tweet back to its original owner by simply following the retweet_ids for each tweet until you find one with no retweet_id.

This method could also work for replies to keep threaded conversations going.

What I have decided to do is create tables for the twits, retwits, replays, hash-tags reference index ext. Which after I developed a class object with methods for each type and did a little object array work to product the results in a processed post format as wanted. This gave me the most options and a easy route to adding or changing services in the long run by leaving each result item as a object.