0 follower

Overall Design

Based on the analysis of the requirements, we identify that our blog application requires four database tables to store data: User, Post, Comment and Tag:

  • User stores the user information, including username and password.
  • Post stores 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.
  • Comment stores post comment information. Each comment is associated with a post and mainly consists of the following columns:
    • name: required, the author name;
    • email: required, the author email;
    • 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).
  • Tag stores post tag information. Each post can have multiple tags, while each tag can also be attached to multiple posts. The Tag table 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 Post and 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

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 /wwwroot/yii/demos/blog/protected/data/schema.sqlite.sql.

We divide the development of our blog application into the following milestones.

  • Milestone 1: creating a prototype of the blog system. It should consist of most of the required functionalities.
  • Milestone 2: completing post management. It includes creating, listing, showing, updating and deleting posts.
  • Milestone 3: completing comment management. It includes creating, listing, approving, updating and deleting post comments.
  • Milestone 4: implementing portlets. It includes user menu, login, tag cloud and recent comments portlets.
  • Milestone 5: final tune-up and deployment.