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.

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.

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.

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_one) REFERENCES users(user_id)
);

Friends table data.

updates table relation
Here updates table relation with three tables users, comments and vote.

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_fk) REFERENCES users(user_id)
);

Status updates:

comments table

Contains status updates comments/replies. Here two relations update_id_fk is FOREIGN KEY to REFERENCESupdates.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_fk) REFERENCES updates(update_id ),
FOREIGN KEY (user_id_fk) REFERENCES users(user_id)
);

vote table

Contains voting clients IP address data (Allows one time voting). Here update_id_fk is FOREIGN KEY to REFERENCESupdates.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_fk) REFERENCES updates(update_id)
);