Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to design a database for a program that is storing and manipulating tweets off of Twitter.

I'm using code to fragment a tweet into words, usernames, and hashtags, so

I'm meeting @President over coffee to talk about my new job #cabinet #woot

would break down into {meeting, over, coffee, talk, about, new, job} (getting rid of words under 2 letters and after stripping punctuation), {president}, and {cabinet,woot}.

With my lack of experience in this area, I'd like to just be able to grow the number of rows in a column as necessary, but my intuition tells me that this would be a nightmare. Another possibility I've considered is just picking an arbitrary number like 10 columns to store words, 5 for usernames, etc., but this would be roughing it a bit, as I don't know for sure how many there will be, and I'd have to throw out what may be valuable information.

As it stands now, I have 3 varchars of sufficient length which hold "meeting over coffee talk about new job", "president", and "cabinet woot". So, whenever I need to grab the information for a particular tweet, I retrieve the row and break up the strings.

Is this the "correct" route? Am I missing an obvious way that will make my life easier? (I had found something online about using array, but I'm using MySQL and it doesn't seem to support that datatype.

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

Edited after a couple of comments by the question asker!:

Better idea for your design: new sample schema

(If you're not familar with these diagrams, that's seven tables, with foreign key links as pictured [the three _Link tables all have two foreign keys referencing the data tables]. It probably looks like I'm overcomplicating things, but trust me, "tokenise once and store in the database" is so much more efficient than "tokenise data each time you retrieve it".)

Sample data, using a simple tweet (ignoring Users for now, it's the same concept though): "test tweet! #howdoesthiswork #newbie"

      Tweet
TweetID   WordCount
   1         2

Tweet_Word_Link
TweetID   WordID
   1         1
   1         2

      Word
WordID    Word
   1      test
   2      tweet

Tweet_Hashtag_Link
TweetID  HashtagID
   1         1
   1         2

      Hashtag
HashtagID  Hashtag
    1      #howdoesthiswork
    2      #newbie

This allows you to easily get all the users linked in a tweet:

SELECT U.UserName
FROM  User U
        INNER JOIN
      Tweet_User_Link TUL ON U.UserID = TUL.UserID
        INNER JOIN
      Tweet T ON TUL.TweetID = T.TweetID
WHERE T.TweetID = <your ID here>

(change table names as appropriate for Hashtags and Words)

Or, slightly more complicated, get the top 10 words for all tweets mentioning a specific hashtag:

SELECT TOP 10 W.Word, COUNT(*) AS WordCount
FROM  Word W
        INNER JOIN
      Tweet_Word_Link TWL ON TWL.WordID = W.WordID
        INNER JOIN
      Tweet T ON TWL.TweetID = T.TweetID
        INNER JOIN
      Tweet_Hashtag_Link THL ON THL.TweetID = T.TweetID
        INNER JOIN
      Hashtag H ON H.HashtagID = THL.HashtagID
WHERE H.Hashtag = <your hashtag here>
GROUP By W.Word

(I've used ANSI SQL syntax, I'm not 100% sure if MySQL supports all of ANSI SQL - but it should be close enough.)

share|improve this answer
    
So would the Tweet_Word_Link table just have 31 columns (id + 30 word columns) and the unused ones filled with null? (in that scenario I was thinking of adding a last column that has the word count) What I'm working on is more in line with the statistics aspect than shuffling and returning, so I'd like to keep the words (as I have the full text of the tweet in another table). –  jonsca Jan 8 '12 at 8:57
    
No, you'd have one row in Tweet_Word_Link for each word in each tweet. i.e. if Tweet #1 is 'testing tweeting', then you'd have two rows in Tweet_Word_Link with the TweetID 1, one of those rows with the WordID of 'testing' and one of them with the WordID of 'tweeting'. –  Simon Righarts Jan 8 '12 at 9:05
    
Ah, then I can use a WHERE in my SQL to grab all of the associated rows? –  jonsca Jan 8 '12 at 9:09
1  
I've rewritten the answer a little (okay, quite a lot), since you said you were aiming more at the stats side of things. Included a couple of sample queries as well. Hope this sets you on the right track. –  Simon Righarts Jan 8 '12 at 9:50
    
wonderful answer! –  xbonez Jan 8 '12 at 11:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.