简单论坛 E-R 模型

总共5张表  论坛版块表(board) 帖子表(post) 用户表(user) 帖子回复表(replay) 后台管理员表(admin) 

-- MySQL Script generated by MySQL Workbench
-- 04/05/19 09:19:48
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

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,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `mydb` ;

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`tp5_bbs_user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`tp5_bbs_user` ;

CREATE TABLE IF NOT EXISTS `mydb`.`tp5_bbs_user` (
  `id` INT(10) UNSIGNED NOT NULL,
  `username` CHAR(20) NOT NULL COMMENT '用户名',
  `password` VARCHAR(45) NOT NULL COMMENT '用户密码',
  `nickname` VARCHAR(10) NOT NULL COMMENT '用户昵称',
  `avatar` VARCHAR(200) NOT NULL COMMENT '用户头像',
  `createat` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户注册时间',
  `createip` INT UNSIGNED NOT NULL COMMENT '用户注册ip',
  `score` INT UNSIGNED NOT NULL COMMENT '用户积分',
  `postcount` INT UNSIGNED NOT NULL COMMENT '用户发帖数',
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`tp5_bbs_board`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`tp5_bbs_board` ;

CREATE TABLE IF NOT EXISTS `mydb`.`tp5_bbs_board` (
  `id` INT(10) UNSIGNED NOT NULL,
  `name` VARCHAR(10) NOT NULL COMMENT '版块名称',
  `icon` VARCHAR(200) NOT NULL COMMENT '版块图标',
  `enabled` TINYINT(1) UNSIGNED NOT NULL COMMENT '是否启用',
  `rules` TEXT NOT NULL COMMENT '版块规则',
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`tp5_bbs_post`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`tp5_bbs_post` ;

CREATE TABLE IF NOT EXISTS `mydb`.`tp5_bbs_post` (
  `id` INT(10) UNSIGNED NOT NULL,
  `title` VARCHAR(40) NOT NULL COMMENT '帖子标题',
  `viewcount` INT UNSIGNED NOT NULL COMMENT '浏览数',
  `replycount` INT UNSIGNED NOT NULL,
  `createat` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '发帖时间',
  `content` TEXT NOT NULL COMMENT '帖子内容',
  `boardid` INT UNSIGNED NOT NULL COMMENT '板块id',
  `userid` INT UNSIGNED NOT NULL COMMENT '用户id',
  PRIMARY KEY (`id`),
  INDEX `fk_post_user_idx` (`userid` ASC),
  INDEX `fk_post_board_idx` (`boardid` ASC),
  CONSTRAINT `fk_post_user`
    FOREIGN KEY (`userid`)
    REFERENCES `mydb`.`tp5_bbs_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_post_board`
    FOREIGN KEY (`boardid`)
    REFERENCES `mydb`.`tp5_bbs_board` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`tp5_bbs_reply`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`tp5_bbs_reply` ;

CREATE TABLE IF NOT EXISTS `mydb`.`tp5_bbs_reply` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '回帖时间',
  `content` TEXT NOT NULL COMMENT '回帖内容',
  `pid` INT(10) UNSIGNED NOT NULL COMMENT '帖子id',
  `uid` INT(10) UNSIGNED NOT NULL COMMENT '用户id',
  PRIMARY KEY (`id`),
  INDEX `fk_reply_post_idx` (`pid` ASC),
  INDEX `fk_reply_user_idx` (`uid` ASC),
  CONSTRAINT `fk_reply_post`
    FOREIGN KEY (`pid`)
    REFERENCES `mydb`.`tp5_bbs_post` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_tp5_bbs_reply_tp5_bbs_user1`
    FOREIGN KEY (`uid`)
    REFERENCES `mydb`.`tp5_bbs_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`tp5_admin`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`tp5_admin` ;

CREATE TABLE IF NOT EXISTS `mydb`.`tp5_admin` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NOT NULL COMMENT '管理员账号',
  `password` VARCHAR(45) NOT NULL COMMENT '管理员密码',
  `loginat` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '管理员最后登录时间',
  `loginip` INT UNSIGNED NOT NULL COMMENT '管理员最后登录ip   ip2long',
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


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