Creating a blog with the Zend Framework part 1: Creating the tables

Okay, I want to experiment some with the Zend Tools by creating a simple blog. Later I would like to do the same in a few other languages but we'll start with the Zend Tool and PHP.

To start, we need to create the tables. I've borrowed a little bit from the Wordpress table setup and simplified it quite a bit. We'll just create three tables: users, posts, and comments. The users table will cover all the people that can create posts and also who can comment. The posts will contain all the post information and the comments will contain all the comment information. The posts table has a one to many relationship with the comments table.

CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT NOT NULL AUTO INCREMENT,
  `user_login` VARCHAR(60) NOT NULL ,
  `user_pass` VARCHAR(64) NOT NULL ,
  `user_name` VARCHAR(50) NOT NULL ,
  `user_email` VARCHAR(100) NOT NULL ,
  `user_url` VARCHAR(100) NOT NULL ,
  `user_registered` DATETIME NOT NULL ,
  `user_activation_key` VARCHAR(60) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `posts` (
  `id` BIGINT NOT NULL AUTO_INCREMENT ,
  `post_author` BIGINT NOT NULL ,
  `post_date` DATETIME NOT NULL ,
  `post_title` TEXT NOT NULL ,
  `post_content` LONGTEXT NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `comments` (
  `id` BIGINT NOT NULL AUTO_INCREMENT ,
  `post_id` BIGINT NOT NULL ,
  `comment_author` TINYTEXT NOT NULL ,
  `comment_author_email` VARCHAR(100) NOT NULL ,
  `comment_author_url` VARCHAR(200) NOT NULL ,
  `comment_author_ip` VARCHAR(100) NOT NULL ,
  `comment_date` DATETIME NOT NULL ,
  `comment_content` TEXT NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;