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!
Great Job Very stuff work & Logic....
very very thanks to your quality information.satyam
Where's the next part?
Nice
thank.
wow! amazing thank you
Really useful article, i cant wait for the next.
Keep rocking!!!!!!
thank.
Amazing Tutorial.
Can i know what tool are you using for mysql for generating such diagrams.. Thank you.
good
amazing..!!
waiting for next lesson :) thanks!
Awesome!
When the next part will come?!
Thanks for this great tutorial!
Yes .. It's good stuff!
nice
Very well explained.. thanks for your hard work.
Excellent. Thank you very much
Wonderfull piece of information.Keep it up Sir!
Your a genious!
There should be more information like this on the web! Shame there is isnt
Thanks!
great tutorial!
How do I create a script when the input to a table of friends it will be friends_one, friends_two, role?
thanks...........
very good ....help for database design..
nice++++
dear friend.
i m not getting the use of friend_two?
Nice work. friend. i was stuck into this problem,and now it's solved. thanx :)
hi srinivas..
this 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??
how 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;)
MySQL Workbench
Great stuff, I love how neat and tidy this article is, very well organised and loving the pictures.
Hi..some one,, can u help me??
how 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