How to manage social network site database perfectly?
I recently start building a social network site database below is my concept of database
Is this is correct or you suggests something else and why
1."user" table stores user details
2."posts" tables stores posts of all user with the user unique id
3."likes" table stores the likes of each posts separately with user, post and comment unique id
4."friends" tables stores the 50001 user ids one id of user and 5000 ids of his/er friends in separate column means 5001 fields is it ok ?
5."comments" table stores comment of all posts which uniques ids of user, post and comment
and all table loads some data on profile.php where user can able to post, like, comment
here is the things which profile.php file do on load
Is this is ok to count the total likes and comments default on any post? and when user click on likes or comments than show him/er the list of peoples who's likes or comments on his/er post? is the above type of database handle this query ? and whats the speed of this process fast/normal/slow ? and "select" command is enough and work fine for this query? Note: I use PHP, MySQL and JavaScript
Please accept my apologize for low formatting and weak English but i try my best
Thank you so much
Seams more or less on target (though a very naive approach) with just a single big NO! on the user friends table.
userId | friendId
Should be the friends table. Just that. When you want to look for user with ID 24 friends you do a
SELECT friendId from user_friends_table WHERE userId = 24
Aside from it start it. The only real way for you to acknowledge how you should have implemented it is to try and fail. Over and over again.