10.1. Tables

The database requirements for this project are rather simple. You need two database tables: one to store the blog posts made by the author and another table to store visitor comments.

+------------+------------------+------+-----+-------------------+----------------+
| Field      | Type             | Null | Key | Default           | Extra          |
+------------+------------------+------+-----+-------------------+----------------+
| POST_ID    | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| POST_TITLE | varchar(50)      | NO   |     |                   |                |
| POST_TEXT  | text             | NO   |     |                   |                |
| POST_DATE  | timestamp        | NO   |     | CURRENT_TIMESTAMP |                |
+------------+------------------+------+-----+-------------------+----------------+

+--------------+------------------+------+-----+-------------------+
| Field        | Type             | Null | Key | Default           |
+--------------+------------------+------+-----+-------------------+
| POST_ID      | int(10) unsigned | NO   | MUL |                   |
| PERSON_NAME  | varchar(50)      | NO   |     |                   |
| POST_COMMENT | varchar(255)     | NO   |     |                   |
| COMMENT_DATE | timestamp        | NO   |     | CURRENT_TIMESTAMP |
+--------------+------------------+------+-----+-------------------+

If you want to require visitors to register before leaving a comment, you could change PERSON_NAME to reference the USER_ID in some user table and then make the appropriate checks before saving the comment to the database. I simply store their names in a VARCHAR column since I am not planning on requiring my visitors to be site members.

Here is the SQL code for the database tables:

CREATE TABLE WROX_BLOG_POST (
    POST_ID       INTEGER UNSIGNED  NOT NULL  AUTO_INCREMENT,
    POST_TITLE    VARCHAR(50)       NOT NULL,
    POST_TEXT     TEXT              NOT NULL,
    POST_DATE     TIMESTAMP         DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (POST_ID)
)
ENGINE=MyISAM DEFAULT CHARACTER SET latin1
    COLLATE latin1_general_cs AUTO_INCREMENT=0;

CREATE TABLE WROX_BLOG_COMMENT (
    POST_ID       INTEGER UNSIGNED  NOT NULL,
    PERSON_NAME   VARCHAR(50)       NOT NULL,
    POST_COMMENT  VARCHAR(255)      NOT NULL,
    COMMENT_DATE  TIMESTAMP         DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (POST_ID)
        REFERENCES WROX_BLOG_POST(POST_ID)
)
ENGINE=MyISAM DEFAULT CHARACTER SET latin1
    COLLATE latin1_general_cs;