데이터베이스 스키마
CREATE TABLE users (
sid int PRIMARY KEY AUTO_INCREMENT,
id varchar(255),
profile_image_url varchar(255),
password varchar(255)
);
CREATE TABLE issues (
id int PRIMARY KEY AUTO_INCREMENT,
user_sid int,
issue_content varchar(255),
issue_name varchar(255),
created_at timestamp,
milestone_name varchar(255),
milestone_id int,
issue_status varchar(255),
assignee id
);
CREATE TABLE assignees (
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(255)
);
CREATE TABLE issue_assignees (
id int PRIMARY KEY AUTO_INCREMENT,
assignee_id int,
issue_id int
);
CREATE TABLE issue_comments (
id int PRIMARY KEY AUTO_INCREMENT,
issue_id int,
comment_id int
);
CREATE TABLE milestones (
id int PRIMARY KEY,
milestone_name varchar(255),
milestone_description varchar(255),
end_date datetime
);
CREATE TABLE labels (
id int PRIMARY KEY,
label_name varchar(255),
color varchar(255),
label_description varchar(255)
);
CREATE TABLE issue_labels (
id int PRIMARY KEY,
label_name int,
issue_id int
);
CREATE TABLE comments (
id int PRIMARY KEY,
merchant_id int,
country_code int,
start_date datetime,
end_date datetime
);
CREATE TABLE emojis (
id int PRIMARY KEY AUTO_INCREMENT,
unicode varchar(255)
);
CREATE TABLE comment_emojis (
id int,
user_id int,
emoji_id int
);
ALTER TABLE issues ADD FOREIGN KEY (user_sid) REFERENCES users (sid);
ALTER TABLE issues ADD FOREIGN KEY (milestone_id) REFERENCES milestones (id);
ALTER TABLE issue_assignees ADD FOREIGN KEY (assignee_id) REFERENCES assignees (id);
ALTER TABLE issue_assignees ADD FOREIGN KEY (issue_id) REFERENCES issues (id);
ALTER TABLE issue_comments ADD FOREIGN KEY (issue_id) REFERENCES issues (id);
ALTER TABLE issue_comments ADD FOREIGN KEY (comment_id) REFERENCES comments (id);
ALTER TABLE issue_labels ADD FOREIGN KEY (label_name) REFERENCES labels (id);
ALTER TABLE issue_labels ADD FOREIGN KEY (issue_id) REFERENCES issues (id);
ALTER TABLE comment_emojis ADD FOREIGN KEY (id) REFERENCES comments (id);
ALTER TABLE comment_emojis ADD FOREIGN KEY (user_id) REFERENCES users (sid);
ALTER TABLE comment_emojis ADD FOREIGN KEY (emoji_id) REFERENCES emojis (id);