Re: [twitter-dev] Re: Sharing MySQL User Table Schema
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 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 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 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 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 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. >
[twitter-dev] Re: Sharing MySQL User Table Schema
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 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 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 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 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.
Re: [twitter-dev] Re: Sharing MySQL User Table Schema
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 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 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 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.
[twitter-dev] Re: Sharing MySQL User Table Schema
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 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 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.
[twitter-dev] Re: Sharing MySQL User Table Schema
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 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.
Re: [twitter-dev] Re: Sharing MySQL User Table Schema
I was planning to modify my twitter app over the coming week to have it work off MySQL (downloaded today), I'll definitely take a look at this first thing tomorrow. Thanks very much for sharing this!!! On 2 April 2010 00:24, DustyReagan wrote: > Oh nice! Didn't know about gist. > > Here it is: http://gist.github.com/352508 > > On Apr 1, 6:16 pm, Abraham Williams <4bra...@gmail.com> wrote: > > You should consider moving the schema to something likehttp:// > gist.github.com/. Then other developers can fork it for their > > modifications, revisions will be kept track of and you can even embed the > > code on your blog and it will always be the latest version. > > > > Abraham > > > > > > > > > > > > On Thu, Apr 1, 2010 at 16:12, DustyReagan 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. > > > > -- > > Abraham Williams | Community Advocate |http://abrah.am > > Digri | Your network just got hotter |http://digri.net > > This email is: [ ] shareable [x] ask first [ ] private. >
[twitter-dev] Re: Sharing MySQL User Table Schema
Oh nice! Didn't know about gist. Here it is: http://gist.github.com/352508 On Apr 1, 6:16 pm, Abraham Williams <4bra...@gmail.com> wrote: > You should consider moving the schema to something > likehttp://gist.github.com/. Then other developers can fork it for their > modifications, revisions will be kept track of and you can even embed the > code on your blog and it will always be the latest version. > > Abraham > > > > > > On Thu, Apr 1, 2010 at 16:12, DustyReagan 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. > > -- > Abraham Williams | Community Advocate |http://abrah.am > Digri | Your network just got hotter |http://digri.net > This email is: [ ] shareable [x] ask first [ ] private.
