Wall Database Design
Wall Script
Follow Me:
Wednesday, August 03, 2011

Wall Database Design

Writing a update on friend wall, this is the most important part in social networking sites. Famous networking sites are like Facebook and Orkut but people calling different like wall and scrap. Now Twitter testing this feature. I have enable this option in labs.9lessons.info. This post explains you how to design database and table relationships for posting a update on friend wall..

Wall Database Design


Live Demo

Note: Before reading the post you have to follow my labs.9lessons.info database design previous posts.



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

updates table
Contains all users status updates data. Here user_id_fk is FOREIGN KEY to REFERENCES users.user_id   and owner 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 ,
`owner` INT NULL ,
FOREIGN KEY (user_id_fkREFERENCES users(user_id),
FOREIGN KEY (ownerREFERENCES users(user_id)
);

Wall Database Design

Friend Wall Update
Insert statement for fried wall update here $session_id refers to login user eg:srinivas (1). Notice the above table data record update_id=4 Srinivas(user_id_fk=1) post a update on Arnold(owner=2) wall.
INSERT INTO updates
(update,user_id_fk,time,host,owner)
VALUES
('$update','$session_id','$time','$ip','$wall_user_id');

User Wall Update
Here user writing on his wall so user_id_fk and owner both same value. Take a look at the about image record update_id=1 Srinivas updated his wall user_id_fk=1 and owner=1
INSERT INTO updates
(update,user_id_fk,time,host,owner)
VALUES
('$update','$session_id','$time','$ip','$session_id');


Home Feed Updates
Data relation between users, updates and friends tables. The following SQL statement users table object a , updates table object b and friends table object c . Here friend_one = '1' refers to users table user_id value.
SELECT a.username, a.email, b.update_id, b.update, b.time, b.vote_up, b.vote_down
FROM users a, updates b, friends c
WHERE b.owner = a.user_id
AND c.friend_one = '1'
AND b.user_id_fk = c.friend_two
ORDER BY b.update_id DESC
LIMIT 15;

Wall Database Design

Profile Updates
Data relations between users and updates tables. The following SQL statement updates table object a and users table object b ,. Here user_id_fk = '1' refers to users table user_id value.
SELECT a.update_id,a.update,a.time,b.username,b.email
FROM updates a,users b
WHERE a.owner=b.user_id
AND a.user_id_fk='1'
ORDER BY a.update_id DESC;
Was this article helpful?
Thanks! Your feedback helps us to improve 9lessons.info


40 comments:

  1. It's a nice one here Srinivas. Thanks

    ReplyDelete
  2. hey .. demo page not found !!! :(

    ReplyDelete
  3. can't view the live demo :( an error Not Found

    The requested URL /high_cpu.htmlsrinivas was not found on this server.

    Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.

    ReplyDelete
  4. This is greate Srinivas, but i didn't quite get the database schema for the `friends` table

    ReplyDelete
  5. @Demo server overload please try after 5 min. I'm planning to buy Amazon Cloud

    ReplyDelete
  6. Hi, is it possible to download this script?

    ReplyDelete
  7. Good work and good guide i made my own but a few of your scripts are very good mainly anything google :)

    ReplyDelete
  8. Can you please tell me where is the friends table?

    ReplyDelete
  9. @Friends Table

    Real part 1 and part 2

    ReplyDelete
  10. can you also show us a "People you may know" and how to import contacts from msn, yahoo

    ReplyDelete
  11. Thank for sharing this, i think this will help many people.

    Just one advice, use PDO instead of old mysql call, because it'll be include by default in the release 6.0

    Don't copy paste the code if you don't understand it (to readers).

    $someObject->somePDOMethod( 'SELECT `a.update_id,a.update`,`a.time,b.username`,`b.email`
    FROM `updates a`,`users b`
    WHERE `a.owner`=`b.user_id`
    AND `a.user_id_fk`=1
    ORDER BY `a.update_id` DESC LIMIT X'
    ,
    $someCustomBool);

    Good Luck and thank you.

    Nicolas

    ReplyDelete
  12. That was nice one from you Srinivas...

    ReplyDelete
  13. my problem now is the session, i cant call the session id, i just can call the session of the users' emails. i'm stuck in the session. i dont want to insert the session id manually, i want to insert it automatically according to email registered...anyone can help me??? need your help so much...thanks...

    ReplyDelete
  14. thanks tamada , but please make files to download

    ReplyDelete
  15. Respect, thank you dude :) ...

    ReplyDelete
  16. Is the updates table the same as the comments table? Would be nice to to have files for download.

    Your Tutorials ROCK!!! THANKS

    ReplyDelete
  17. Hi. This will help many people including me. While I'm building a website. thanks

    ReplyDelete
  18. Thanks to share these codes i selected sclnetwrk as my mini project

    ReplyDelete
  19. can you make a downloadable of this tutorial please?

    ReplyDelete
  20. Its verry cool this tutorial, is exactly wath i needed to make my school project :D Its very cool that people like you help others with this kind of tutorials :D

    Thanks men, you're awesome :D

    ReplyDelete
  21. Hi i followed your tutorial and changed the update function from your wallscript4.0

    but instead of c.friend_one and c.friend_two mine is P.user_id and P.user_id_2

    The following only shows the friends messages and not the users own messages:

    This is the WHERE section of the query

    WHERE M.uid_fk=U.uid AND P.user_id='$uid' AND M.uid_fk=P.user_id_2

    I've been messing with this query for ages

    David

    ReplyDelete
  22. I need to buy the wall script 4.0 and to get detail tutorial on how to use it with lab application. How do i make a payment since my country in not welcomed

    ReplyDelete
  23. Hi srinivas
    please give me a script about if i had already add someone as friend then in place of "add connection" button show "Drop Connection" button

    ReplyDelete
  24. nice article , user for me.
    i have one doubt that how i know user is my friend or not. if i send request to my friend then which table of column should be updates so that he is added to my friend list..
    can u help me ?.....
    satyawan shinde

    ReplyDelete
  25. nice article , user for me.
    i have one doubt that how i know user is my friend or not. if i send request to my friend then which table of column should be updates so that he is added to my friend list..
    can u help me ?.....
    satyawan shinde

    ReplyDelete
  26. nice article , user for me.
    i have one doubt that how i know user is my friend or not. if i send request to my friend then which table of column should be updates so that he is added to my friend list..
    can u help me ?.....
    satyawan shinde

    ReplyDelete
  27. hi,
    please show the friend table also.

    nice blog.

    ReplyDelete

Make in India