Saturday, August 18, 2012

Creating Database Design for Forum Application Software Yii Framework

After determining the features, it is time to creating database design for forum application software Yii Framework.
  • Level: Table level is used as the identity of the User level. As already exist on the features that the user in this application consists of three types of user, namely Admin, Moderator and User Ordinary. With the table level, becomes possible if we want to modify the user level.
  • Category: To divide the existing thread into certain categories. Categories can also be added, subtracted and modified as needed.
  • User: This table is used to store user information. User table is also used for the authentication process when the user login process.
  • Thread: Storing all information contained in the forum thread.
  • Comment: Keep all the comments from each thread
  • News: Saving the information inputted by the admin news which will then be displayed on the main page of the application.
  • Raputation: Keep all judgments given by the user to a user.
  • Threadstar: Storing information on an assessment carried out by the user thread.
The following SQL database on which I have made:
CREATE TABLE IF NOT EXISTS `comment` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) DEFAULT NULL,
 `content` text NOT NULL,
 `user_id` int(11) NOT NULL,
 `thread_id` int(11) NOT NULL,
 `datePost` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `thread_id` (`thread_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `category` (
 id` int(11) NOT NULL AUTO_INCREMENT,
 `category` varchar(100) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `level` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `level` varchar(50) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `news` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(200) NOT NULL,
 `content` text NOT NULL,
 `photo` varchar(200) NOT NULL,
 `user` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `user` (`user`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `raputation` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `jenis` tinyint(1) NOT NULL,
 `pemberi_id` int(11) NOT NULL,
 `penerima_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `pemberi_id` (`pemberi_id`),
 KEY `penerima_id` (`penerima_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `thread` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) NOT NULL,
 `content` text NOT NULL,
 `user_id` int(11) NOT NULL,
 `category_id` int(11) NOT NULL,
 `datePost` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `threadstar` (
 `is` int(11) NOT NULL AUTO_INCREMENT,
 `nilai` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
 `thread_id` int(11) NOT NULL,
 PRIMARY KEY (`is`),
 KEY `user_id` (`user_id`),
 KEY `thread_id` (`thread_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(20) NOT NULL,
 `password` varchar(50) NOT NULL,
 `saltPassword` varchar(50) NOT NULL,
 `email` varchar(50) NOT NULL,
 `joinDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `level_id` int(11) NOT NULL,
 `avatar` varchar(30) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `username` (`username`),
 KEY `level_id` (`level_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

ALTER TABLE `comment`
 ADD CONSTRAINT `comment_ibfk_1` FOREIGN KEY (`user_id`)
REFERENCES `user` (`id`) ON UPDATE CASCADE,
 ADD CONSTRAINT `comment_ibfk_2` FOREIGN KEY (`thread_id`)
REFERENCES `thread` (`id`) ON UPDATE CASCADE;

ALTER TABLE `news`
 ADD CONSTRAINT `news_ibfk_1` FOREIGN KEY (`user`) REFERENCES
`user` (`id`) ON UPDATE CASCADE;

ALTER TABLE `raputation`
 ADD CONSTRAINT `raputation_ibfk_1` FOREIGN KEY (`pemberi_id`)
REFERENCES `user` (`id`),
 ADD CONSTRAINT `raputation_ibfk_2` FOREIGN KEY (`penerima_id`)
REFERENCES `user` (`id`);

ALTER TABLE `thread`
 ADD CONSTRAINT `thread_ibfk_3` FOREIGN KEY (`user_id`)
REFERENCES `user` (`id`) ON UPDATE CASCADE,
 ADD CONSTRAINT `thread_ibfk_4` FOREIGN KEY (`category_id`)
REFERENCES `category` (`id`) ON UPDATE CASCADE;

ALTER TABLE `threadstar`
 ADD CONSTRAINT `threadstar_ibfk_3` FOREIGN KEY (`user_id`)
REFERENCES `user` (`id`) ON UPDATE CASCADE,

 ADD CONSTRAINT `threadstar_ibfk_4` FOREIGN KEY (`thread_id`)
REFERENCES `thread` (`id`) ON UPDATE CASCADE;

ALTER TABLE `user`
 ADD CONSTRAINT `user_ibfk_1` FOREIGN KEY (`level_id`) REFERENCES `level` (`id`) ON UPDATE CASCADE;

After you finish creating the database, please create a new Yii Framework application with the name of  "forum", after that connect Yii application to the MySql database that we have made earlier, and activate the feature Gii on Yii Framework. If you've managed to do both, please generate CRUD operations on all existing tables in your database. You can look at my previous post.

4 comments:

  1. is there any feature in IC as YII have GII. ?? what i meant that as YII have GII for CRUD operation , is there any feature in codeignator Sibghatullah

    ReplyDelete
  2. Great work!Thank you so much for sharing this valuable information.Iam also looking for this.Here i suggest you the best custom software development company in india, namely Yiioverflow.Yiioverflow mainly concentrate on JS Development, Hybrid App Development ,Yii Framework Development.

    ReplyDelete

Yii Framework Tutorials