[AWS] CI/CD 배포 4 - MySql 설정

1. MySql 시작하기

2. 테이블 생성

create database bodykeydb;

use bodykeydb; 


CREATE TABLE user_tb (
    goal_fat FLOAT,
    goal_muscle FLOAT,
    goal_weight FLOAT,
    height FLOAT,
    id INT AUTO_INCREMENT,
    total_coin INT,
    birth TIMESTAMP(6),
    created_at TIMESTAMP(6),
    gender VARCHAR(255),
    name VARCHAR(255),
    password VARCHAR(255),
    phone VARCHAR(255),
    user_img VARCHAR(255),
    username VARCHAR(255) UNIQUE,
    PRIMARY KEY (id)
) DEFAULT CHARSET=utf8mb4;


CREATE TABLE activity_tb (
    drink_water INT,
    id INT AUTO_INCREMENT,
    user_id INT,
    walking INT,
    created_at TIMESTAMP(6),
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES user_tb(id)
) DEFAULT CHARSET=utf8mb4;

CREATE TABLE bodydata_tb (
    fat FLOAT,
    id INT AUTO_INCREMENT,
    muscle FLOAT,
    user_id INT,
    weight FLOAT,
    created_at TIMESTAMP(6),
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES user_tb(id)
) DEFAULT CHARSET=utf8mb4;

CREATE TABLE meal_tb (
    id INT AUTO_INCREMENT,
    user_id INT,
    created_at TIMESTAMP(6),
    eat_time VARCHAR(255),
    meal_img VARCHAR(255),
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES user_tb(id)
) DEFAULT CHARSET=utf8mb4;

CREATE TABLE food_tb (
    carbo FLOAT,
    fat FLOAT,
    gram INT,
    id INT AUTO_INCREMENT,
    kcal FLOAT,
    protein FLOAT,
    created_at TIMESTAMP(6),
    name VARCHAR(255),
    PRIMARY KEY (id)
) DEFAULT CHARSET=utf8mb4;

CREATE TABLE eat_tb (
    food_id INT,
    id INT AUTO_INCREMENT,
    meal_id INT,
    quantity INT,
    PRIMARY KEY (id),
    FOREIGN KEY (food_id) REFERENCES food_tb(id),
    FOREIGN KEY (meal_id) REFERENCES meal_tb(id)
) DEFAULT CHARSET=utf8mb4;

CREATE TABLE challenge_tb (
    coin INT,
    id INT AUTO_INCREMENT,
    period INT,
    walking INT,
    created_at TIMESTAMP(6),
    background_img VARCHAR(255),
    badge_img VARCHAR(255),
    challenge_name VARCHAR(255),
    content TEXT,
    distance VARCHAR(255),
    sub_title VARCHAR(255),
    PRIMARY KEY (id)
) DEFAULT CHARSET=utf8mb4;


CREATE TABLE attend_challenge_tb (
    challenge_id INT,
    id INT AUTO_INCREMENT,
    status BOOLEAN,
    total_walking INT,
    user_id INT,
    closing_time TIMESTAMP(6),
    created_at TIMESTAMP(6),
    opening_time TIMESTAMP(6),
    PRIMARY KEY (id),
    FOREIGN KEY (challenge_id) REFERENCES challenge_tb(id),
    FOREIGN KEY (user_id) REFERENCES user_tb(id)
) DEFAULT CHARSET=utf8mb4;


CREATE TABLE survey_tb (
    id INT AUTO_INCREMENT,
    created_at TIMESTAMP(6),
    status VARCHAR(255),
    title VARCHAR(255),
    PRIMARY KEY (id)
) DEFAULT CHARSET=utf8mb4;


CREATE TABLE do_survey_tb (
    id INT AUTO_INCREMENT,
    survey_id INT,
    user_id INT,
    created_at TIMESTAMP(6),
    PRIMARY KEY (id),
    FOREIGN KEY (survey_id) REFERENCES survey_tb(id),
    FOREIGN KEY (user_id) REFERENCES user_tb(id)
) DEFAULT CHARSET=utf8mb4;


CREATE TABLE survey_question_tb (
    id INT AUTO_INCREMENT,
    survey_id INT,
    created_at TIMESTAMP(6),
    question_item VARCHAR(255),
    PRIMARY KEY (id),
    FOREIGN KEY (survey_id) REFERENCES survey_tb(id)
) DEFAULT CHARSET=utf8mb4;


CREATE TABLE question_choice_tb (
    choice_number INT,
    id INT AUTO_INCREMENT,
    survey_id INT,
    survey_question_id INT,
    created_at TIMESTAMP(6),
    choice_item VARCHAR(255),
    PRIMARY KEY (id),
    FOREIGN KEY (survey_id) REFERENCES survey_tb(id),
    FOREIGN KEY (survey_question_id) REFERENCES survey_question_tb(id)
) DEFAULT CHARSET=utf8mb4;


CREATE TABLE choice_answer_tb (
    id INT AUTO_INCREMENT,
    question_choice_id INT,
    survey_id INT,
    survey_question_id INT,
    user_id INT,
    created_at TIMESTAMP(6),
    PRIMARY KEY (id),
    FOREIGN KEY (question_choice_id) REFERENCES question_choice_tb(id),
    FOREIGN KEY (survey_id) REFERENCES survey_tb(id),
    FOREIGN KEY (survey_question_id) REFERENCES survey_question_tb(id),
    FOREIGN KEY (user_id) REFERENCES user_tb(id)
) DEFAULT CHARSET=utf8mb4;