ERD

ERD

-- MySQL Script generated by MySQL Workbench
-- Tue Jul 25 10:59:26 2023
-- 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 =
        'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema issue_tracker
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `issue_tracker`.`user_account`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `issue_tracker`.`user_account`
(
    `id`          INT          NOT NULL AUTO_INCREMENT,
    `login_id`    VARCHAR(45)  NOT NULL,
    `password`    VARCHAR(512) NOT NULL,
    `profile_url` VARCHAR(512) NOT NULL,
    `is_deleted`  TINYINT      NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `issue_tracker`.`milestone`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `issue_tracker`.`milestone`
(
    `id`          INT          NOT NULL AUTO_INCREMENT,
    `name`        VARCHAR(45)  NOT NULL,
    `description` VARCHAR(256) NULL,
    `due_date`    TIMESTAMP    NOT NULL,
    `is_deleted`  TINYINT      NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `issue_tracker`.`issue`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `issue_tracker`.`issue`
(
    `id`              INT          NOT NULL AUTO_INCREMENT,
    `title`           VARCHAR(45)  NOT NULL,
    `is_open`         TINYINT      NOT NULL,
    `created_at`      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `content`         VARCHAR(256) NULL,
    `is_deleted`      TINYINT      NOT NULL DEFAULT 0,
    `user_account_id` INT          NULL,
    `milestone_id`    INT          NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `issue_tracker`.`label`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `issue_tracker`.`label`
(
    `id`               INT         NOT NULL AUTO_INCREMENT,
    `name`             VARCHAR(45) NOT NULL,
    `description`      VARCHAR(45) NULL,
    `font_color`       VARCHAR(45) NOT NULL,
    `background_color` VARCHAR(45) NOT NULL,
    `is_deleted`       TINYINT     NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `issue_tracker`.`comment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `issue_tracker`.`comment`
(
    `id`              INT          NOT NULL AUTO_INCREMENT,
    `content`         VARCHAR(256) NOT NULL,
    `created_at`      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `is_deleted`      TINYINT      NOT NULL DEFAULT 0,
    `user_account_id` INT          NOT NULL,
    `issue_id`        INT          NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `issue_tracker`.`issue_label`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `issue_tracker`.`issue_label`
(
    `id`       INT NOT NULL AUTO_INCREMENT,
    `issue_id` INT NOT NULL,
    `label_id` INT NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `issue_tracker`.`issue_assignee`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `issue_tracker`.`issue_assignee`
(
    `id`              INT NOT NULL AUTO_INCREMENT,
    `issue_id`        INT NOT NULL,
    `user_account_id` INT NOT NULL,
    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;