database ontwerp

CREATE  TABLE IF NOT EXISTS `mydb`.`POSTTAG` (
  `PTID` INT NOT NULL ,
  `PID` INT NOT NULL ,
  `TNID` INT NOT NULL ,
  PRIMARY KEY (`PTID`) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `mydb`.`COMMENT` (
  `CID` INT NOT NULL ,
  `CCOMMENT` TEXT NOT NULL ,
  `CAUTHOR` VARCHAR(45) NOT NULL ,
  `CDATE` INT NOT NULL ,
  `CIP` INT NOT NULL ,
  PRIMARY KEY (`CID`) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `mydb`.`POSTCOMMENT` (
  `PCID` INT NOT NULL ,
  `PID` INT NOT NULL ,
  `CID` INT NOT NULL ,
  PRIMARY KEY (`PCID`) ,
  INDEX `FK_C` (`CID` ASC) ,
  CONSTRAINT `FK_C`
    FOREIGN KEY (`CID` )
    REFERENCES `mydb`.`COMMENT` (`CID` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `mydb`.`POST` (
  `PID` INT NOT NULL ,
  `PTITLE` VARCHAR(150) NOT NULL ,
  `PCONTENT` TEXT NOT NULL ,
  `PAUTHOR` VARCHAR(45) NOT NULL ,
  `PDATE` INT NOT NULL ,
  PRIMARY KEY (`PID`) ,
  INDEX `FK_PT` (`PID` ASC) ,
  INDEX `FK_PC` (`PID` ASC) ,
  CONSTRAINT `FK_PT`
    FOREIGN KEY (`PID` )
    REFERENCES `mydb`.`POSTTAG` (`PID` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `FK_PC`
    FOREIGN KEY (`PID` )
    REFERENCES `mydb`.`POSTCOMMENT` (`PID` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `mydb`.`TAG` (
  `TNID` INT NOT NULL ,
  `TAGNAME` VARCHAR(30) NOT NULL ,
  PRIMARY KEY (`TNID`) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `mydb`.`POSTTAG` (
  `PTID` INT NOT NULL ,
  `PID` INT NOT NULL ,
  `TNID` INT NOT NULL ,
  PRIMARY KEY (`PTID`) )
ENGINE = InnoDB;
 
CREATE  TABLE IF NOT EXISTS `mydb`.`COMMENT` (
  `CID` INT NOT NULL ,
  `CCOMMENT` TEXT NOT NULL ,
  `CAUTHOR` VARCHAR(45) NOT NULL ,
  `CDATE` INT NOT NULL ,
  `CIP` INT NOT NULL ,
  PRIMARY KEY (`CID`) )
ENGINE = InnoDB;
 
CREATE  TABLE IF NOT EXISTS `mydb`.`POSTCOMMENT` (
  `PCID` INT NOT NULL ,
  `PID` INT NOT NULL ,
  `CID` INT NOT NULL ,
  PRIMARY KEY (`PCID`) ,
  INDEX `FK_C` (`CID` ASC) ,
  CONSTRAINT `FK_C`
    FOREIGN KEY (`CID` )
    REFERENCES `mydb`.`COMMENT` (`CID` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
 
CREATE  TABLE IF NOT EXISTS `mydb`.`POST` (
  `PID` INT NOT NULL ,
  `PTITLE` VARCHAR(150) NOT NULL ,
  `PCONTENT` TEXT NOT NULL ,
  `PAUTHOR` VARCHAR(45) NOT NULL ,
  `PDATE` INT NOT NULL ,
  PRIMARY KEY (`PID`) ,
  INDEX `FK_PT` (`PID` ASC) ,
  INDEX `FK_PC` (`PID` ASC) ,
  CONSTRAINT `FK_PT`
    FOREIGN KEY (`PID` )
    REFERENCES `mydb`.`POSTTAG` (`PID` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `FK_PC`
    FOREIGN KEY (`PID` )
    REFERENCES `mydb`.`POSTCOMMENT` (`PID` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
 
CREATE  TABLE IF NOT EXISTS `mydb`.`TAG` (
  `TNID` INT NOT NULL ,
  `TAGNAME` VARCHAR(30) NOT NULL ,
  PRIMARY KEY (`TNID`) )
ENGINE = InnoDB;