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

Live Demo

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

• Part 1: Database Design Create Tables and Relationships with SQL
• Part 2: Join Tables Relationships with SQL

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.

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_fk) REFERENCES users(user_id),
FOREIGN KEY (owner) REFERENCES users(user_id)
);

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;

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;