Thursday, 9 May 2013
Facebook StumbleUpon Twitter Google+ Pin It

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

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

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

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,
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
DOB: 12/24/1986

No comments: