News Portal Database Design

News Portal Database Design

This article will provide you with the list of tables and entities for every table in the development of news portal system. The team will later provide a video tutorial on how to create the database in PHPMyAdmin.

tblnews – this is the main table of the system, this is where the news will be stored. The tblnews has 9 fields.

• news_id – this is the primary key of the table.
• category_id – the news will be grouped according to their type and its content; this is the foreign key that links to the tblnewscategory table.
• date_posted – this column refers to the posting date of the news or article.
• news_title – the title of the content or news.
• news_content – the body of the news.
• date_updated – this will record the date when the author updates the contents of the news.
• news_status – this column has two values 0 and 1, 0 for unpublished, 1 for published.
• comment_status – the author can set the news if it can accept comment or not.
• author_id – this column represents the author of the article. This is a foreign key that links to the tblauthor.

Create SQL Statement – the statement below is used to create the tblnews, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblnews` (
`news_id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` int(11) NOT NULL,
`date_posted` date NOT NULL,
`news_title` varchar(100) NOT NULL,
`news_content` text NOT NULL,
`date_updated` date NOT NULL,
`news_status` int(1) NOT NULL,
`comment_status` int(1) NOT NULL,
`author_id` int(11) NOT NULL,
PRIMARY KEY (`news_id`),
KEY `category_id` (`category_id`,`author_id`),
KEY `author_id` (`author_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblnewscategory – this table will store the list of news category of the system. it has 3 columns as presented below.

• category_id – primary key of the table.
• category_name – name of the category.
• category_description – brief description about the category.

Create SQL Statement – the statement below is used to create the tblnewscategory, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblnewscategory` (
`category_id` int(11) NOT NULL AUTO_INCREMENT,
`category_name` varchar(30) NOT NULL,
`category_description` varchar(50) NOT NULL,
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblauthor – authors are the one who can create and post an article to the system. This is the table that stores the information about the authors. It has 8 columns.

• author_id – primary key of the table.
• author_name – complete name of the author.
• author_display_name – the name that will be displayed in the article. This column serves as the code name of the author.
• author_email – email address of the author.
• author_account_status – account status refers to the login status of the author, 0 is for deactivated, and 1 is for activated.
• author_profile – the profile image of the author.
• username – the desired username of the author.
• password – the desired password of the author, at least minimum of 8 characters.

Create SQL Statement – the statement below is used to create the tblauthor, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblauthor` (
`author_id` int(11) NOT NULL AUTO_INCREMENT,
`author_name` varchar(50) NOT NULL,
`author_display_name` varchar(30) NOT NULL,
`author_email` varchar(30) NOT NULL,
`author_account_status` int(1) NOT NULL,
`author_profile` longblob NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
PRIMARY KEY (`author_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblsubscriber – subscriber refers to the users/visitors that is registered in the website.

• subscriber_id – primary key of the table.
• subscriber_display_name – the code name of the subscriber, they can choose to display their real name or their code name.
• subscriber_name – this field will store the real name of the subscriber.
• subscriber_email – email address of the subscriber, they can receive updates from the site through their email address.
• subscriber_profile – the image profile of the subscriber.
• username – the desired username of the subscriber.
• password – the desired password of the subscriber, at least minimum of 8 characters.
• account_status – account status refers to the login status of the subscriber, 0 is for deactivated, and 1 is for activated.
• date_joined – the registration date of the subscriber.
• date_approved – the account will be reviewed by the administrator and this column refers to the approval date.

Create SQL Statement – the statement below is used to create the tblsubscriber, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblsubscriber` (
`subscriber_id` int(11) NOT NULL AUTO_INCREMENT,
`subscriber_display_name` varchar(30) NOT NULL,
`subscriber_name` varchar(50) NOT NULL,
`subscriber_email` varchar(30) NOT NULL,
`subscriber_profile` longblob NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`account_status` int(1) NOT NULL,
`date_joined` date NOT NULL,
`date_approved` date NOT NULL,
PRIMARY KEY (`subscriber_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcomment – this is the table that stores the comments of the subscribers to the news/article/post. The table has 7 columns.

• comment_id – primary key of the table.
• comment_content – the comment of the subscriber will be stored in this column.
• subscriber_id – this is a foreign key that links to the tblsubscriber, this column refers to the subscriber who posted a comment into the article.
• news_id – this is a foreign key that links to the tblnews table.
• comment_date – the posting date of the comment.
• comment_status – comments will not be automatically posted on the website, it will be reviewed first by the administrator/moderator of the system.
• user_id – the user who approves the comment.

Create SQL Statement – the statement below is used to create the tblcomment, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblcomment` (
`comment_id` int(11) NOT NULL AUTO_INCREMENT,
`comment_content` varchar(100) NOT NULL,
`subscriber_id` int(11) NOT NULL,
`news_id` int(11) NOT NULL,
`comment_date` date NOT NULL,
`comment_status` int(1) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`comment_id`),
KEY `subscriber_id` (`subscriber_id`,`user_id`),
KEY `news_id` (`news_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbluser – this will store the administrator and moderator information of the system.

• user_id – primary key of the table.
• user_display_name – the code name of the user.
• user_complete_name – the real name of the user.
• username – username assigned to the user.
• password – the desired password of the user, at least minimum of 8 characters.
• user_profile – image profile of the user.
• user_type – user category of the user; 0 for administrator and 1 for moderator.

Create SQL Statement – the statement below is used to create the tbluser, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tbluser` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`user_display_name` varchar(30) NOT NULL,
`user_complete_name` varchar(30) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`user_profile` longblob NOT NULL,
`user_type` int(1) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblbackup

• backup_id
• backup_date
• backup_file
• user_id

Create SQL Statement – the statement below is used to create the tblbackup, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tblbackup` (
`backup_id` int(11) NOT NULL AUTO_INCREMENT,
`backup_date` date NOT NULL,
`backup_file` varchar(50) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`backup_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Constraints for dumped tables
—
— Constraints for table `tblbackup`
—
ALTER TABLE `tblbackup`
ADD CONSTRAINT `tblbackup_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblcomment`
—
ALTER TABLE `tblcomment`
ADD CONSTRAINT `tblcomment_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblcomment_ibfk_1` FOREIGN KEY (`subscriber_id`) REFERENCES `tblsubscriber` (`subscriber_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblcomment_ibfk_2` FOREIGN KEY (`news_id`) REFERENCES `tblnews` (`news_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblnews`
—
ALTER TABLE `tblnews`
ADD CONSTRAINT `tblnews_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `tblauthor` (`author_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblnews_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `tblnewscategory` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE;

Download .sql file

You may visit our Facebook page for more information, inquiries, and comments. Please subscribe also to our YouTube Channel to receive free capstone projects resources and computer programming tutorials.

Hire our team to do the project.