Nigel, It depends on what you're trying to accomplish. For example, for Friend Or Follow I only store the user's latest tweet. I disregard all other tweets. In that case storing the last status object with the user object makes the most sense, and does not create duplicate rows. If you're storing more than the user's last tweet, absolutely, you should have a 'tweet' table and a 'user' table linked by the user's twitter_id.
On Apr 2, 6:55 am, Nigel Legg <nigel.l...@gmail.com> wrote: > Dusty, just took a look at that, good stuff. > Wouldn't it be better database design to have separate tables for user and > status, and only update user details if they have changed? This design > suggests you will have a lot of duplicate data in the database. Just a > thought. > Nigel. > > On 2 April 2010 02:26, DustyReagan <dustyrea...@gmail.com> wrote: > > > > > Hey Damon! > > > FoF is missing several new and new(ish) fields, particularly because > > it takes ages to update the DB. I'll add the verified field to gist! > > > Thanks for pointing out the protected field! I think I set it to > > varchar(5) because Twitter sometimes sends back 'false' and sometimes > > sends back '1', and most of the time sends back null. (It may send > > back '0', and 'true' as well, I can't remember.) I save it to the DB > > the way Twitter sends it, and do the mapping on read. Probably the > > smart thing to do is, like you said, make it a tinyint(1) then scrub > > the data before insert. > > > My new strategy to database updates is 1) to do as many as possible in > > one batch 2) put Friend Or Follow in read-only mode, so it still > > works, but the DB user cache/table isn't updating. I can stop doing > > inserts for a few days before anyone seems to really notice. A more > > better solution would probably be some sorta' hot swap, ie: point the > > app to a backup copy of the DB, do the updates on the primary DB, then > > point the app back to the primary DB. It's easier for me to just go > > read-only for a stint though. Updates are a headache with YesSQL for > > sure! > > > On Apr 1, 7:15 pm, Damon C <d.lifehac...@gmail.com> wrote: > > > Curious why you're using a varchar field for protected as opposed to > > > tinyint(1)/boolean? I don't see a "verified" field either, not sure if > > > that's necessary for FoF. > > > > Other than that, most of my stuff is pretty similar although I'm not > > > quite as discerning on the lengths of the fields. I usually just do > > > varchar(255). > > > > Do you have a strategy/opinion for when Twitter adds additional fields > > > like geo, verified, etc? This is one of the primary reasons I've been > > > considering leaving YesSQL since I have to shut down my site for hours > > > just to do an ALTER. :( > > > > Damon > > > > On Apr 1, 4:12 pm, DustyReagan <dustyrea...@gmail.com> wrote: > > > > > So, it occurs to me how many developers must be reinventing the MySQL > > > > schema for the User object. I've started work on optimizing my > > > > database for Friend Or Follow, and thought it'd be cool to share my > > > > schema and collaborate with other YesSQL users. > > > > > Here's where I'm starting: > >http://dustyreagan.com/twitter-mysql-user-object-table-schema/ > > > > > Leave comments here or on my blog and I'll update the MySQL in the > > > > main post. It'd be nice to have this for other Twitter objects as well. -- To unsubscribe, reply using "remove me" as the subject.