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 varchar
s 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.