I'm building a microblogging system like twitter and I need help in optimizing the database structure.
I have tables "user_info", "posts" and "friends". Each time when user logs in, lets say user X, all latest posts of friends of X should be displayed.
So it means that when user logs in I need to:
1)Get all friends of X with query#1
2)Get all latest posts of each friend with query#2 (i make a long query using php like "...from posts where author = A or author = B or author = C or...or author = Z", where A, B, C, D... are friends that I get with query#1 in step 1)
3)In "posts" table in 'author' column user_id is saved, so for each post I need also to Get information about author like name, sex from "user_info" table using user_id from posts.
To optimize it a little bit I use JOIN to make steps 2 and 3 with ONE request instead of two.
To optimize it more I could use VIEWs, but the point is that the WHERE clause is always different, because different users log in -> "SELECT ..... FROM...JOIN...ON () WHERE friend1 = $currentUser".
So question #1: are my database strucure and idea right?
question #2: is it possible to use VIEWs if WHERE clause is changing each time?