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. This post explains you how to design database and table relationships for posting a update on friend wall..

users table

Parent table contains all users data, while registration data storing in this table.

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 

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;

-By Parthiv Patel

Parthiv Patel

Bhaishri Info Solution

Sr. PHP Developer

Limdi Chowk, AT PO. Nar, Di. Anand

Nar, Gujarat

388150

India

pparthiv2412@gmail.com

7383343029

DOB: 12/24/1986