Overall Design

Based on the analysis of the requirements, we decide to use the following database tables to store the persistent data for our blog application:

The following entity-relation (ER) diagram shows the table structure and relationships about the above tables.

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.

Info: We name all our table names and column names in lower case. This is because different DBMS often have different case-sensitivity treatment and we want to avoid troubles like this.

We also prefix all our tables with tbl_. This serves for two purposes. First, the prefix introduces a namespace to these tables in case when they need to coexist with other tables in the same database, which often happens in a shared hosting environment where a single database is being used by multiple applications. Second, using table prefix reduces the possibility of having some table names that are reserved keywords in DBMS.

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

$Id$