🍥ERD 설계

+ token 테이블의 login_id는 user 테이블의 login_id와 동일합니다.

schema.sql

SET foreign_key_checks = 0;
DROP TABLE IF EXISTS issue_label;
DROP TABLE IF EXISTS comment;
DROP TABLE IF EXISTS assignee;
DROP TABLE IF EXISTS issue;
DROP TABLE IF EXISTS label;
DROP TABLE IF EXISTS milestone;
DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS token;
SET foreign_key_checks = 1;

CREATE TABLE `user` (
    `user_id` bigint  NOT NULL AUTO_INCREMENT ,
    `login_id` varchar(30) NOT NULL UNIQUE ,
    `password` varchar(50)  NOT NULL ,
    `image` varchar(200)  NOT NULL DEFAULT '' ,
    PRIMARY KEY (
                 `user_id`
        )
);

CREATE TABLE `milestone` (
    `milestone_id` bigint  NOT NULL AUTO_INCREMENT,
    `name` varchar(50)  NOT NULL ,
    `deadline` datetime ,
    `description` varchar(200) ,
    `status` varchar(10)  NOT NULL DEFAULT 'open' ,
    PRIMARY KEY (
              `milestone_id`
     )
);

CREATE TABLE `label` (
    `label_id` bigint  NOT NULL AUTO_INCREMENT,
    `name` varchar(50)  NOT NULL ,
    `description` varchar(200) ,
    `background_color` varchar(10)  NOT NULL ,
    `text_color` varchar(10)  NOT NULL ,
    `is_deleted` boolean  NOT NULL DEFAULT false ,
    PRIMARY KEY (
              `label_id`
     )
);

CREATE TABLE `issue` (
    `issue_id` bigint  NOT NULL AUTO_INCREMENT ,
    `author_id` bigint  NOT NULL ,
    `milestone_id` bigint ,
    `title` varchar(50)  NOT NULL ,
    `contents` text ,
    `created_at` datetime  NOT NULL DEFAULT NOW() ,
    `status` varchar(10)  NOT NULL DEFAULT 'open' ,
    PRIMARY KEY (
              `issue_id`
     ) ,
    FOREIGN KEY (`author_id`) REFERENCES `user` (`user_id`),
    FOREIGN KEY (`milestone_id`) REFERENCES `milestone` (`milestone_id`)
);

CREATE TABLE `assignee` (
    `assignee_id` bigint NOT NULL AUTO_INCREMENT,
    `issue_id` bigint  NOT NULL ,
    `user_id` bigint  NOT NULL ,
    PRIMARY KEY (
                 `assignee_id`
        ) ,
    FOREIGN KEY (`issue_id`) REFERENCES `issue` (`issue_id`) ,
    FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
);

CREATE TABLE `comment` (
    `comment_id` bigint  NOT NULL AUTO_INCREMENT,
    `issue_id` bigint  NOT NULL ,
    `author_id` bigint  NOT NULL ,
    `contents` text  NOT NULL ,
    `created_at` datetime  NOT NULL DEFAULT NOW() ,
    `is_deleted` boolean  NOT NULL DEFAULT false ,
    PRIMARY KEY (
                `comment_id`
       ) ,
    FOREIGN KEY (`issue_id`) REFERENCES `issue` (`issue_id`) ,
    FOREIGN KEY (`author_id`) REFERENCES `user` (`user_id`)
);

CREATE TABLE `issue_label` (
    `issue_label_id` bigint NOT NULL AUTO_INCREMENT,
    `issue_id` bigint  NOT NULL ,
    `label_id` bigint  NOT NULL ,
    PRIMARY KEY (
                `issue_label_id`
       ) ,
    FOREIGN KEY (`issue_id`) REFERENCES `issue` (`issue_id`) ,
    FOREIGN KEY (`label_id`) REFERENCES `label` (`label_id`)
);

CREATE TABLE `token` (
    `refresh_token` varchar(200) NOT NULL,
    `login_id` varchar(30) NOT NULL,
    PRIMARY KEY (
            `refresh_token`
    )
);

data.sql

INSERT INTO user (login_id, password, image) VALUES ('ayaan1234', '123456', 'https://avatars.githubusercontent.com/u/57559288?s=80&u=22fcaa63715a65dfa747506fffe592b0acbb2846&v=4');
INSERT INTO user (login_id, password, image) VALUES ('jian1234', '123456', 'https://avatars.githubusercontent.com/u/97204689?s=80&u=34888415e252f727b1d3a849e7f1387a20ce3696&v=4');
INSERT INTO user (login_id, password, image) VALUES ('bono1234', '123456', 'https://avatars.githubusercontent.com/u/70848762?s=80&v=4');
INSERT INTO user (login_id, password, image) VALUES ('hana1234', '123456', 'https://avatars.githubusercontent.com/u/117690393?s=80&u=ba9f18d1ab53f87cbe07a308e103d26d6bcbf221&v=4');
INSERT INTO user (login_id, password, image) VALUES ('khundi1234', '123456', 'https://avatars.githubusercontent.com/u/57666791?v=4');
INSERT INTO user (login_id, password, image) VALUES ('puban1234', '123456', 'https://avatars.githubusercontent.com/u/86706366?s=80&v=4');

INSERT INTO milestone (name, deadline, description) VALUES ('be-milestone1', '2023-08-04T00:00:00', '백엔드');
INSERT INTO milestone (name, deadline, description) VALUES ('be-milestone2', '2023-08-11T00:00:00', '백엔드');
INSERT INTO milestone (name, deadline, description) VALUES ('fe-milestone1', '2023-08-04T00:00:00', '프론트엔드');
INSERT INTO milestone (name, deadline, description) VALUES ('fe-milestone2', '2023-08-11T00:00:00', '프론트엔드');
INSERT INTO milestone (name, deadline) VALUES ('sprint1', NOW());

INSERT INTO label (name, description, background_color, text_color) VALUES ('be', '백엔드', '#ADC151', '#000000');
INSERT INTO label (name, description, background_color, text_color) VALUES ('fe', '프론트엔드', '#5F9EEE', '#000000');
INSERT INTO label (name, description, background_color, text_color) VALUES ('feature', '새로운 기능', '##0075ca', '#ffffff');
INSERT INTO label (name, background_color, text_color) VALUES ('docs', '#662208', '#ffffff');

INSERT INTO issue (author_id, milestone_id, title, contents) VALUES (1, 1, '기능 구현1', '내용 컨텐츠 내용 컨텐츠 내용 컨텐츠');
INSERT INTO issue (author_id, milestone_id, title, contents) VALUES (2, 1, '기능 구현2', '내용 컨텐츠 내용 컨텐츠 내용 컨텐츠');
INSERT INTO issue (author_id, milestone_id, title, contents, status) VALUES (3, 1, '기능 구현3', '내용 컨텐츠 내용 컨텐츠 내용 컨텐츠', 'deleted');
INSERT INTO issue (author_id, milestone_id, title, contents, status) VALUES (4, 1, '기능 구현4', '내용 컨텐츠 내용 컨텐츠 내용 컨텐츠', 'closed');
INSERT INTO issue (author_id, title, contents) VALUES (5, '기능 구현5', '내용 컨텐츠 내용 컨텐츠 내용 컨텐츠');
INSERT INTO issue (author_id, title, contents) VALUES (6, '기능 구현6', '내용 컨텐츠 내용 컨텐츠 내용 컨텐츠');

INSERT INTO assignee (issue_id, user_id) VALUES (1, 1);
INSERT INTO assignee (issue_id, user_id) VALUES (1, 2);
INSERT INTO assignee (issue_id, user_id) VALUES (2, 1);

INSERT INTO comment (issue_id, author_id, contents) VALUES (1, 3, '선택 미션의 경우, 원래 프로젝트 기간 동안 못하는 것이 (삐빅-)정상입니다.');
INSERT INTO comment (issue_id, author_id, contents) VALUES (1, 4, '???????????????');

INSERT INTO issue_label (issue_id, label_id) VALUES (1, 1);
INSERT INTO issue_label (issue_id, label_id) VALUES (1, 3);
INSERT INTO issue_label (issue_id, label_id) VALUES (2, 4);