Dusty, Sure if you only store the last tweet, that's cool - should have asked before jumping to conclusions. For what I want, it'll definitely be better to have a separate table, and only update what needs changing. When I'm done messing round with OAuth, and sorting out my GUI, I'll be looking at that side of things again - had a whole lot more to do than I thought.
On 2 April 2010 19:17, DustyReagan <dustyrea...@gmail.com> wrote: > 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. >