Based on the analysis of the requirements, we identify that our blog application requires four database tables to store data:
Userstores the user information, including username and password.
Poststores post information. It mainly consists of the following columns:
title: required, title of the post;
content: required, body content of the post which uses the Markdown format;
status: required, status of the post, which can be one of following values:
draft: the post is in draft and is not visible to public;
published: the post is published to public;
archived: the post is outdated and is not visible to public.
tags: optional, a list of comma-separated words categorizing the post.
Commentstores post comment information. Each comment is associated with a post and mainly consists of the following columns:
name: required, the author name;
website: optional, the author website URL;
content: required, the comment content which uses the Markdown format.
status: required, status of the comment, which indicates whether the comment is approved (value 1) or not (value 0).
Tagstores post tag information. Each post can have multiple tags, while each tag can also be attached to multiple posts. The
Tagtable is mainly used by the tag cloud portlet which needs to calculate the use frequency of each tag.
The following entity-relation (ER) diagram shows the table structure and relationships about the above tables. Note that the relationship between
Tag is many-to-many, we use the
PostTag table to decouple this relationship into two one-to-many relationships.
Entity-Relation Diagram of the Blog Database
Complete SQL statements corresponding to the above ER diagram may be found in the blog demo. In our Yii installation, they are in the file
We divide the development of our blog application into the following milestones.