Database Design Create Tables and Relationships with SQL
Wall Script
Follow Me:
Wednesday, April 21, 2010

Database Design Create Tables and Relationships with SQL

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.

9lessons Labs Database Design.

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.
Database Design Flow Diagram

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/9lessons
CREATE 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
);

Data storing like this here password stored in encrypted formate tutorial link.
user table database design

friends table
Contains friend relation data. Here fri_one is FOREIGN KEY to REFERENCES users.user_id Eg : labs.9lessons.info/friends/srinivas
CREATE 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_oneREFERENCES users(user_id)
);

Friends table data.
Friends Database Design Flow Diagram

updates table relation
Here updates table relation with three tables users, comments and vote.
Database Design Flow Diagram

updates table
Contains all users status updates data. Here user_id_fk is FOREIGN KEY to REFERENCES users.user_id Eg : labs.9lessons.info/9lessons
CREATE 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_fkREFERENCES users(user_id)
);

Status updates:
Status Updates database design

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_fkREFERENCES updates(update_id ),
FOREIGN KEY (user_id_fkREFERENCES users(user_id)
);
Status comments database Design Flow Diagram

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 here
CREATE 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_fkREFERENCES updates(update_id)
);
voting system database Design Flow Diagram

Was this article helpful?
Thanks! Your feedback helps us to improve 9lessons.info


43 comments:

  1. Great Job Very stuff work & Logic....

    ReplyDelete
  2. very very thanks to your quality information.satyam

    ReplyDelete
  3. Where's the next part?

    ReplyDelete
  4. wow! amazing thank you

    ReplyDelete
  5. Really useful article, i cant wait for the next.
    Keep rocking!!!!!!

    ReplyDelete
  6. Amazing Tutorial.

    ReplyDelete
  7. Can i know what tool are you using for mysql for generating such diagrams.. Thank you.

    ReplyDelete
  8. waiting for next lesson :) thanks!

    ReplyDelete
  9. Awesome!
    When the next part will come?!

    ReplyDelete
  10. Very well explained.. thanks for your hard work.

    ReplyDelete
  11. Excellent. Thank you very much

    ReplyDelete
  12. Wonderfull piece of information.Keep it up Sir!

    ReplyDelete
  13. Your a genious!
    There should be more information like this on the web! Shame there is isnt

    Thanks!

    ReplyDelete
  14. great tutorial!

    ReplyDelete
  15. How do I create a script when the input to a table of friends it will be friends_one, friends_two, role?

    ReplyDelete
  16. very good ....help for database design..

    ReplyDelete
  17. dear friend.
    i m not getting the use of friend_two?

    ReplyDelete
  18. Nice work. friend. i was stuck into this problem,and now it's solved. thanx :)

    ReplyDelete
  19. 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?

    ReplyDelete
  20. 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..

    ReplyDelete
  21. what do you use for creating db schemas? they're pretty good;)

    ReplyDelete
  22. Great stuff, I love how neat and tidy this article is, very well organised and loving the pictures.

    ReplyDelete
  23. 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..

    ReplyDelete
  24. 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

    ReplyDelete
  25. This will only returns the friends of user id 1,
    Suppose if user id 2 is friend of userd id 1, And user 2 logs in then he will not be able to see user1

    ReplyDelete
  26. 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.

    ReplyDelete
  27. Hello, 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.

    I hope you'll clear my doubt. :)

    Thanks

    ReplyDelete
  28. Srinivas, It's really very helpful, very clean and neat explanation..Many thanks for your help...

    ReplyDelete

Make in India