This post explains how to design typical relationaships database for socialmedia web application. Today I'm presenting my labs.9lessons application database relations design and SQL with diagrams. I hope you like this post.
labs.9lessons.info Data Modeling Design
Here you can see the five tables between relation flow user, friends, updates, comments and vote. This following image generated by using Mysql Workbench tool.
Here users table relation with three tables friends, updates and comments.
users table
Parent table contains all users data, while registration data storing in this table. Eg : labs.9lessons.info/9lessonsCREATE TABLE `users` (
`user_id` INT NOT NULL primary key AUTO_INCREMENT ,
`username` VARCHAR(45) NULL unique,
`password` VARCHAR(45) NULL ,
`email` VARCHAR(45) NULL ,
`twitter_token` VARCHAR(99) NULL ,
`twitter_token_secret` VARCHAR(99) NULL
);
`user_id` INT NOT NULL primary key AUTO_INCREMENT ,
`username` VARCHAR(45) NULL unique,
`password` VARCHAR(45) NULL ,
`email` VARCHAR(45) NULL ,
`twitter_token` VARCHAR(99) NULL ,
`twitter_token_secret` VARCHAR(99) NULL
);
Data storing like this here password stored in encrypted formate tutorial link.
friends table
Contains friend relation data. Here fri_one is FOREIGN KEY to REFERENCES users.user_id Eg : labs.9lessons.info/friends/srinivasCREATE TABLE `friends` (
`friend_id` INT NOT NULL primary key AUTO_INCREMENT ,
`friend_one` INT NULL ,
`friend_two` INT NULL ,
`role` VARCHAR(5) NULL ,
FOREIGN KEY (friend_one) REFERENCES users(user_id)
);
`friend_id` INT NOT NULL primary key AUTO_INCREMENT ,
`friend_one` INT NULL ,
`friend_two` INT NULL ,
`role` VARCHAR(5) NULL ,
FOREIGN KEY (friend_one) REFERENCES users(user_id)
);
Friends table data.
updates table relation
Here updates table relation with three tables users, comments and vote.
updates table
Contains all users status updates data. Here user_id_fk is FOREIGN KEY to REFERENCES users.user_id Eg : labs.9lessons.info/9lessonsCREATE TABLE `updates` (
`update_id` INT NOT NULL primary key AUTO_INCREMENT ,
`update` TEXT NULL ,
`time` INT NULL ,
`host` VARCHAR(45) NULL , // Client IP address
`vote_up` INT NULL , // Up votes data
`vote_down` INT NULL , // Down votes
`user_id_fk` INT NULL ,
FOREIGN KEY (user_id_fk) REFERENCES users(user_id)
);
`update_id` INT NOT NULL primary key AUTO_INCREMENT ,
`update` TEXT NULL ,
`time` INT NULL ,
`host` VARCHAR(45) NULL , // Client IP address
`vote_up` INT NULL , // Up votes data
`vote_down` INT NULL , // Down votes
`user_id_fk` INT NULL ,
FOREIGN KEY (user_id_fk) REFERENCES users(user_id)
);
Status updates:
comments table
Contains status updates comments/replies. Here two relations update_id_fk is FOREIGN KEY to REFERENCES updates.update_id and user_id_fk is FOREIGN KEY to REFERENCES users.user_id Eg:click here CREATE TABLE `comments` (
`comment_id` INT NOT NULL primary key AUTO_INCREMENT ,
`comment` TEXT NULL ,
`time` INT NULL ,
`host` VARCHAR(45) NULL ,
`update_id_fk` INT NULL ,
`user_id_fk` INT NULL ,
FOREIGN KEY (update_id_fk) REFERENCES updates(update_id ),
FOREIGN KEY (user_id_fk) REFERENCES users(user_id)
);
`comment_id` INT NOT NULL primary key AUTO_INCREMENT ,
`comment` TEXT NULL ,
`time` INT NULL ,
`host` VARCHAR(45) NULL ,
`update_id_fk` INT NULL ,
`user_id_fk` INT NULL ,
FOREIGN KEY (update_id_fk) REFERENCES updates(update_id ),
FOREIGN KEY (user_id_fk) REFERENCES users(user_id)
);
vote table
Contains voting clients IP address data (Allows one time voting). Here update_id_fk is FOREIGN KEY to REFERENCES updates.update_id Reference tutorials link click hereCREATE TABLE `vote` (
`vote_id` INT NOT NULL primary key AUTO_INCREMENT ,
`vote_host` VARCHAR(45) NULL ,
`update_id_fk` INT NULL ,
FOREIGN KEY (update_id_fk) REFERENCES updates(update_id)
);
`vote_id` INT NOT NULL primary key AUTO_INCREMENT ,
`vote_host` VARCHAR(45) NULL ,
`update_id_fk` INT NULL ,
FOREIGN KEY (update_id_fk) REFERENCES updates(update_id)
);
Good stuff!
ReplyDeleteGreat Job Very stuff work & Logic....
ReplyDeletevery very thanks to your quality information.satyam
ReplyDeleteWhere's the next part?
ReplyDeleteNice
ReplyDeletethank.
ReplyDeletewow! amazing thank you
ReplyDeleteReally useful article, i cant wait for the next.
ReplyDeleteKeep rocking!!!!!!
thank.
ReplyDeleteAmazing Tutorial.
ReplyDeleteCan i know what tool are you using for mysql for generating such diagrams.. Thank you.
ReplyDeletegood
ReplyDeleteamazing..!!
ReplyDeletewaiting for next lesson :) thanks!
ReplyDeleteAwesome!
ReplyDeleteWhen the next part will come?!
Thanks for this great tutorial!
ReplyDeleteYes .. It's good stuff!
ReplyDeletenice
ReplyDeleteVery well explained.. thanks for your hard work.
ReplyDeleteExcellent. Thank you very much
ReplyDeleteWonderfull piece of information.Keep it up Sir!
ReplyDeleteYour a genious!
ReplyDeleteThere should be more information like this on the web! Shame there is isnt
Thanks!
great tutorial!
ReplyDeleteHow do I create a script when the input to a table of friends it will be friends_one, friends_two, role?
ReplyDeletethanks...........
ReplyDeletevery good ....help for database design..
ReplyDeletenice++++
ReplyDeletedear friend.
ReplyDeletei m not getting the use of friend_two?
Nice work. friend. i was stuck into this problem,and now it's solved. thanx :)
ReplyDeletehi srinivas..
ReplyDeletethis is friends table
SELECT a.username, a.email
FROM users a, friends b
WHERE a.user_id = b.friend_two
AND b.friend_one = '1'
AND b.role = 'fri'
ORDER BY b.friend_id DESC
LIMIT 30 ;
pertanyaan saya adalah bagaimana cara menampilkan user yang bukan teman, dengan menggunakan mysql?
Hi..some one,, can u help me??
ReplyDeletehow to display SUGGEST FRIENDS with php and MYsql statement??
for the FRIENDS TABLE , you can see above..
what do you use for creating db schemas? they're pretty good;)
ReplyDeleteMySQL Workbench
ReplyDeleteGreat stuff, I love how neat and tidy this article is, very well organised and loving the pictures.
ReplyDeleteHi..some one,, can u help me??
ReplyDeletehow to display SUGGEST FRIENDS with php and MYsql statement??
for the FRIENDS TABLE , you can see above..
hi srinivas, your examples are brilliant. Thanks. Do you have an asp.net example of this by any chance? I want to implement all these features in my project and I only know asp.net
ReplyDeleteGood Example !!!
ReplyDeleteThis will only returns the friends of user id 1,
ReplyDeleteSuppose if user id 2 is friend of userd id 1, And user 2 logs in then he will not be able to see user1
In friends table you have Friend_two. What is Friend_two and why you should use it? without using it you can return user1 friends or user2 without seeing each others friends.
ReplyDeletevery nice illustration..
ReplyDeleteNice!
ReplyDeleteHello, this is very nice article. But I have one question. What is the user of column "friend_two" in "friends" table and from you are inserting data in that column.
ReplyDeleteI hope you'll clear my doubt. :)
Thanks
Srinivas, It's really very helpful, very clean and neat explanation..Many thanks for your help...
ReplyDelete