ERD

DDL

-- 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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

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

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

CREATE TABLE IF NOT EXISTS `mydb`.`TEAM` (
  `TEAM_ID` INT NOT NULL COMMENT '팀아이디',
  `UNIQUE_ID` VARCHAR(255) NOT NULL COMMENT '팀구별아이디',
  `NAME` VARCHAR(20) NULL COMMENT '팀이름',
  `COLOR` VARCHAR(20) NULL DEFAULT 'WHITE' COMMENT '색상',
  PRIMARY KEY (`TEAM_ID`))
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `mydb`.`MEMBER` (
  `MEMBER_ID` INT NOT NULL COMMENT '회원아이디',
  `NICKNAME` VARCHAR(20) NULL COMMENT '회원닉네임',
  `UNIQUE_ID` VARCHAR(255) NULL COMMENT '회원구별아이디',
  `PROFILE_IMG_URL` VARCHAR(255) NULL COMMENT '회원프로필이미지URL',
  `DUMMY` TINYINT NULL COMMENT '더미회원여부',
  PRIMARY KEY (`MEMBER_ID`))
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `mydb`.`TEAM_MEMBER` (
  `TEAM_MEMBER_ID` INT NOT NULL COMMENT '팀회원아이디',
  `TEAM_ID` INT NOT NULL COMMENT '팀아이디',
  `MEMBER_ID` INT NOT NULL COMMENT '회원아이디',
  `CAPTAIN` TINYINT NOT NULL COMMENT '팀장여부',
  PRIMARY KEY (`TEAM_MEMBER_ID`),
  INDEX `MEMBER_ID_idx` (`MEMBER_ID` ASC) VISIBLE,
  INDEX `TEAM_ID_idx` (`TEAM_ID` ASC) VISIBLE,
  CONSTRAINT `TEAM_ID`
    FOREIGN KEY (`TEAM_ID`)
    REFERENCES `mydb`.`TEAM` (`TEAM_ID`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `MEMBER_ID`
    FOREIGN KEY (`MEMBER_ID`)
    REFERENCES `mydb`.`MEMBER` (`MEMBER_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `mydb`.`TIMETABLE` (
  `TIMETABLE_ID` INT NOT NULL COMMENT '이미지아이디',
  `IMG_URL` VARCHAR(255) NOT NULL COMMENT '이미지URL',
  `MEMBER_ID` INT NOT NULL,
  PRIMARY KEY (`TIMETABLE_ID`),
  INDEX `MEMBER_ID_idx` (`MEMBER_ID` ASC) VISIBLE,
  CONSTRAINT `MEMBER`
    FOREIGN KEY (`MEMBER_ID`)
    REFERENCES `mydb`.`MEMBER` (`MEMBER_ID`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `mydb`.`SUBJECT` (
  `SUBJECT` INT NOT NULL COMMENT '과목아이디',
  `WEEK_DAY` VARCHAR(45) NOT NULL COMMENT '요일',
  `BGN_TIME` INT NOT NULL COMMENT '시작시간',
  `END_TIME` INT NOT NULL COMMENT '종료시간',
  `TIMETABLE_ID` INT NOT NULL COMMENT '시간표아이디',
  PRIMARY KEY (`SUBJECT`),
  INDEX `TIMETABLE_ID_idx` (`TIMETABLE_ID` ASC) VISIBLE,
  CONSTRAINT `TIMETABLE_ID`
    FOREIGN KEY (`TIMETABLE_ID`)
    REFERENCES `mydb`.`TIMETABLE` (`TIMETABLE_ID`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `mydb`.`TEAM_TIMETABLE` (
  `TEAM_TIMETABLE_ID` INT NOT NULL COMMENT '팀이미지아이디',
  `TEAM_ID` INT NOT NULL COMMENT '팀아이디',
  `TIMETABLE_ID` INT NOT NULL COMMENT '시간표아이디',
  PRIMARY KEY (`TEAM_TIMETABLE_ID`),
  INDEX `IMAGE_ID_idx` (`TIMETABLE_ID` ASC) VISIBLE,
  INDEX `TEAM_ID_idx` (`TEAM_ID` ASC) VISIBLE,
  CONSTRAINT `TEAM`
    FOREIGN KEY (`TEAM_ID`)
    REFERENCES `mydb`.`TEAM` (`TEAM_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `IMAGE_ID`
    FOREIGN KEY (`TIMETABLE_ID`)
    REFERENCES `mydb`.`TIMETABLE` (`TIMETABLE_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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