Please Check Out My Practice Design

[Agent Cosmic]

Hi guys,

I'm learning PHP and MySQL now, so I did a simple forum database in MySQL Workbench. I hope you guys can check it out and point out anything I should that note of. Also I would appreciate it if you could share your learning resources (like blogs/tutorials, not api reference) and recommend some database design where I could study from. Thanks!

Here's the EER model
http://i33.servimg.com/u/f33/12/53/83/84/eer10.png

and the SQL code just in case

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `blogum` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `blogum` ;

-- -----------------------------------------------------
-- Table `blogum`.`user`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `blogum`.`user` (
  `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `email` VARCHAR(320) NOT NULL ,
  `username` VARCHAR(12) NOT NULL ,
  `password` VARCHAR(12) NOT NULL ,
  `avatar` VARCHAR(2083) NULL ,
  PRIMARY KEY (`user_id`) ,
  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC) ,
  UNIQUE INDEX `username_UNIQUE` (`username` ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `blogum`.`profile`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `blogum`.`profile` (
  `user_id` INT UNSIGNED NOT NULL ,
  `bio` VARCHAR(255) NULL ,
  `gender` VARCHAR(12) NULL ,
  `website` VARCHAR(255) NULL ,
  `age` INT NULL ,
  PRIMARY KEY (`user_id`) ,
  INDEX `fk_profile_user` (`user_id` ASC) ,
  CONSTRAINT `fk_user_profile`
    FOREIGN KEY (`user_id` )
    REFERENCES `blogum`.`user` (`user_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `blogum`.`forum`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `blogum`.`forum` (
  `forum_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(32) NOT NULL ,
  PRIMARY KEY (`forum_id`) ,
  UNIQUE INDEX `forum_id_UNIQUE` (`forum_id` ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `blogum`.`post`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `blogum`.`post` (
  `post_id` INT(11) NOT NULL ,
  `title` VARCHAR(255) NOT NULL ,
  `content` TEXT NOT NULL ,
  `date_created` DATETIME NOT NULL ,
  `user_id` INT(10) UNSIGNED NOT NULL ,
  `forum_id` INT(10) UNSIGNED NOT NULL ,
  PRIMARY KEY (`post_id`) ,
  INDEX `fk_post_user` (`user_id` ASC) ,
  INDEX `fk_post_forum` (`forum_id` ASC) ,
  CONSTRAINT `fk_post_forum`
    FOREIGN KEY (`forum_id` )
    REFERENCES `blogum`.`forum` (`forum_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_post_user`
    FOREIGN KEY (`user_id` )
    REFERENCES `blogum`.`user` (`user_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci;


-- -----------------------------------------------------
-- Table `blogum`.`comment`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `blogum`.`comment` (
  `comment_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `content` TEXT NOT NULL ,
  `date_created` DATETIME NOT NULL ,
  `post_id` INT NOT NULL ,
  PRIMARY KEY (`comment_id`) ,
  UNIQUE INDEX `comment_id_UNIQUE` (`comment_id` ASC) ,
  INDEX `fk_comment_post` (`post_id` ASC) ,
  CONSTRAINT `fk_comment_post`
    FOREIGN KEY (`post_id` )
    REFERENCES `blogum`.`post` (`post_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

[griswolf]

Some quick thoughts:

• A password should never be stored "clear" in the database, so the password field should be password_hash, and it probably needs to be a bit larger to hold a good hash.
• Why is the user profile in a different table from the user? They have a one-for-one relationship, so no need for another table. (Unless some users don't have profiles, or have a different kind, or something)

[debasisdas]

You need to merge the fields of profile with user.

Profile_id should be added to profile table.

[Agent Cosmic]

Thank you so much for the reply and the great advices.