DataBase 명세서

DataBase 명세서

개요

• MySQL

ERD 다이어 그램

Schema SQL

CREATE TABLE USER(
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    login_id varchar(20) UNIQUE NOT NULL,
    password varchar(255) NOT NULL,
    img varchar(255) NOT NULL,
    type tinyint(1) NOT NULL,
    created_at DATE NOT NULL
);

CREATE TABLE COMMENT(
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    issue_id int NOT NULL,
    user_id int NOT NULL,
    body text,
    emoji varchar(10),
    created_at DATE NOT NULL,
    FOREIGN KEY (issue_id) REFERENCES ISSUE(id) ON UPDATE CASCADE,
    FOREIGN KEY (user_id) REFERENCES USER(id) ON UPDATE CASCADE
);

CREATE TABLE MILESTONE(
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(20) UNIQUE NOT NULL,
    description varchar(255),
    due_date date,
    state tinyint(1) NOT NULL,
    created_at date
);

CREATE TABLE ISSUE(
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title varchar(255) NOT NULL,
    body text,
    user_id int NOT NULL,
    state tinyint(1) NOT NULL,
    milestone_id int NOT NULL,
    created_at date,
    closed_at date,
    FOREIGN KEY (user_id) REFERENCES USER(id) ON UPDATE CASCADE,
    FOREIGN KEY (milestone_id) REFERENCES MILESTONE(id) ON UPDATE CASCADE
);

CREATE TABLE LABEL (
    id INT auto_increment NOT NULL PRIMARY KEY,
    name VARCHAR(20),
    description varchar(100),
    color varchar(10),
    created_at DATE
); 

CREATE TABLE TAG (
    id INT auto_increment NOT NULL PRIMARY KEY,
    issue_id INT NOT NULL,
    label_id INT NOT NULL,
    FOREIGN KEY (issue_id) REFERENCES ISSUE(id) ON UPDATE CASCADE,
    FOREIGN KEY (label_id) REFERENCES LABEL(id) ON UPDATE CASCADE
);

CREATE TABLE ASSIGNEE (
    id INT auto_increment NOT NULL PRIMARY KEY,
    issue_id INT NOT NULL,
    user_id INT NOT NULL,
    FOREIGN KEY (issue_id) REFERENCES ISSUE(id) ON UPDATE CASCADE,
    FOREIGN KEY (user_id) REFERENCES USER(id) ON UPDATE CASCADE
);

CREATE TABLE EVENT (
    id INT auto_increment NOT NULL PRIMARY KEY,
    issue_id INT NOT NULL,
    actor varchar(20),
    log varchar(100),
    created_at DATE,
    FOREIGN KEY (issue_id) REFERENCES ISSUE(id) ON UPDATE CASCADE
);

// mysql 이모지 사용

my.cnf파일 변경
[mysqld]
collation-server = utf8mb4_unicode_ci
character-set-server = utf8mb4
skip-character-set-client-handshake

sql
ALTER DATABASE itdb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;