Solution for SOLO CHALLENGE: Modeling a One to Many Database
Solution for Challenge: SOLO CHALLENGE: Modeling a One to Many Database. Started 2014-02-10T06:32:23+00:00
####One to Many Relationship: Users & Tweets
Each user posts 1 to many tweets (technically, 0 to many, right? I'll go with 1 for consistency throughout)
Each tweet originates from one to one user
Since only one direction has a many potentential, this is a 1 to many relationship between users and tweets.
####Many to Many Relationship: Users & Followers
Each user has one two many followers
Each follower follows one to many users
Since a user follows many users and is followed by many users, this is a many to many relationship. However, I think this is a
special case of a many to many relationship because it is between two of the same object type (that is, a follower is a user). Because of this, it requires only two tables and separate records invert the
follower_id foreign keys when there is a bidirectional follow relationship between two users.
###Reflection I have experience with relational databases and the relational model. While that was helpful, I think that experience actually tripped me up a bit in trying to mentally map my instinctive relational ER diagramming perspective to the object model diagram we depict in SQL designer. (I think that makes sense..?) I'm still working on a better understanding of how the two intersect and their role.
I tried to further model the twitter User - List relationship but couldn't quite wrap my head around how to depict the complex subtype relationships between users and lists (creator 1..1; subscriber 0..many; member 1..many). I started an ER diagram thinking that if I could successfully create the relational model then I could work on understanding the map to the object model. Unfortunately, I got stuck on splitting the multiple many to manys between the same entities without creating traps and aborted for the time being. Hopefully these things will become clearer as we move forward!
-- all the tweets for a certain user id SELECT tweet_id, tweet_text FROM tweets WHERE user_id = 123456678923402; -- the tweets for a certain user id that were made after last Wednesday SELECT tweet_id, tweet_text FROM tweets WHERE created_at >= date('2014-02-07') AND (user_id = 123456678923402); -- all the tweets associated with a given user's twitter handle SELECT t.tweet_id, t.tweet_text FROM tweets t JOIN users u ON (t.user_id = u.user_id) WHERE u.user_handle = 'carolineartz'; -- the username associated with a given tweet id SELECT u.username FROM users u JOIN tweets t ON (u.user_id = t.user_id) WHERE t.tweet_id = 345673123456; -- all followers and the followed date for a given user's twitter handle SELECT f.follower_id, date(f.followed_at) FROM user_followers f, users u WHERE (f.user_id = u.user_id) AND (u.user_handle = 'carolineartz');