1. 데이터베이스 구성

2. 데이터베이스 URL

🔗 DB Diagram 보기

3. 데이터베이스 이미지

4. DB SQL 구문

CREATE TABLE users (
    id CHAR(36) PRIMARY KEY NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    nickname VARCHAR(12) NOT NULL,
    is_privacy_agree BOOLEAN NOT NULL,
    created_at DATETIME(6) NOT NULL,
    updated_at DATETIME(6) NOT NULL,
    deleted_at DATETIME(6)
);

CREATE TABLE user_profiles (
    user_id CHAR(36) PRIMARY KEY NOT NULL,
    age_group VARCHAR(20) NOT NULL,
    gender VARCHAR(20) NOT NULL,
    height DECIMAL(5,2) NOT NULL,
    weight DECIMAL(5,2) NOT NULL,
    sleep_minutes INT,
    exercise_minutes INT,
    CONSTRAINT fk_user_profile_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);

CREATE TABLE user_profile_forbidden_food (
    user_id CHAR(36) NOT NULL,
    food VARCHAR(100) NOT NULL,
    FOREIGN KEY (user_id)
        REFERENCES user_profiles(user_id)
        ON DELETE CASCADE
);

CREATE TABLE refresh_tokens (
    user_id CHAR(36) PRIMARY KEY NOT NULL,
    token VARCHAR(255) NOT NULL,
    CONSTRAINT fk_refresh_token_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);

CREATE TABLE posts (
    id CHAR(36) PRIMARY KEY NOT NULL,
    title VARCHAR(80) NOT NULL,
    content TEXT NOT NULL,
    view_count INT NOT NULL DEFAULT 0,
    user_id CHAR(36) NOT NULL,
    created_at DATETIME(6) NOT NULL,
    updated_at DATETIME(6) NOT NULL,
    deleted_at DATETIME(6),
    CONSTRAINT fk_post_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);

CREATE TABLE comments (
    id CHAR(36) PRIMARY KEY NOT NULL,
    post_id CHAR(36) NOT NULL,
    user_id CHAR(36) NOT NULL,
    parent_id CHAR(36),
    target_id CHAR(36),
    content TEXT NOT NULL,
    created_at DATETIME(6) NOT NULL,
    updated_at DATETIME(6) NOT NULL,
    deleted_at DATETIME(6),
    CONSTRAINT fk_comment_post FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    CONSTRAINT fk_comment_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_comment_parent FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE,
    CONSTRAINT fk_comment_target FOREIGN KEY (target_id) REFERENCES comments(id) ON DELETE CASCADE
);

CREATE TABLE likes (
    id CHAR(36) PRIMARY KEY NOT NULL,
    user_id CHAR(36) NOT NULL,
    target_type VARCHAR(20) NOT NULL,
    target_id CHAR(36) NOT NULL,
    created_at DATETIME(6) NOT NULL,
    updated_at DATETIME(6) NOT NULL,
    UNIQUE KEY uq_likes_user_target (user_id, target_type, target_id),
    CONSTRAINT fk_like_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);

CREATE TABLE images (
    id CHAR(36) PRIMARY KEY NOT NULL,
    object_key VARCHAR(255) NOT NULL,
    target_type VARCHAR(20) NOT NULL,
    target_id CHAR(36) NOT NULL,
    sort_order INT,
    created_at DATETIME(6) NOT NULL,
    updated_at DATETIME(6) NOT NULL,
    deleted_at DATETIME(6)
);

CREATE TABLE diet_recommendations (
    id CHAR(36) PRIMARY KEY NOT NULL,
    user_id CHAR(36) NOT NULL,
    meal_type VARCHAR(20) NOT NULL,
    recommended_date DATE NOT NULL,
    source VARCHAR(20) NOT NULL,
    created_at DATETIME(6) NOT NULL,
    CONSTRAINT fk_diet_recommend_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);

CREATE TABLE diet_recommendation_items (
    id CHAR(36) PRIMARY KEY NOT NULL,
    recommendation_id CHAR(36) NOT NULL,
    food_name VARCHAR(100) NOT NULL,
    calories INT,
    protein DECIMAL(5,2),
    fat DECIMAL(5,2),
    carbs DECIMAL(5,2),
    CONSTRAINT fk_diet_recommend_item
        FOREIGN KEY (recommendation_id)
        REFERENCES diet_recommendations(id)
        ON DELETE CASCADE
);

CREATE TABLE diet_logs (
    id CHAR(36) PRIMARY KEY NOT NULL,
    user_id CHAR(36) NOT NULL,
    meal_type VARCHAR(20) NOT NULL,
    log_date DATE NOT NULL,
    source VARCHAR(20) NOT NULL,
    recommendation_id CHAR(36),
    created_at DATETIME(6) NOT NULL,
    UNIQUE KEY uq_diet_log_user_date_meal (user_id, log_date, meal_type),
    CONSTRAINT fk_diet_log_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_diet_log_recommend FOREIGN KEY (recommendation_id) REFERENCES diet_recommendations(id) ON DELETE SET NULL
);

CREATE TABLE diet_log_items (
    id CHAR(36) PRIMARY KEY NOT NULL,
    diet_log_id CHAR(36) NOT NULL,
    food_name VARCHAR(100) NOT NULL,
    calories INT,
    protein DECIMAL(5,2),
    fat DECIMAL(5,2),
    carbs DECIMAL(5,2),
    CONSTRAINT fk_diet_log_item
        FOREIGN KEY (diet_log_id)
        REFERENCES diet_logs(id)
        ON DELETE CASCADE
);

CREATE TABLE workout_recommendations (
    id CHAR(36) PRIMARY KEY NOT NULL,
    user_id CHAR(36) NOT NULL,
    recommended_date DATE NOT NULL,
    source VARCHAR(20) NOT NULL,
    created_at DATETIME(6) NOT NULL,
    CONSTRAINT fk_workout_recommend_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);

CREATE TABLE workout_recommendation_exercises (
    id CHAR(36) PRIMARY KEY NOT NULL,
    recommendation_id CHAR(36) NOT NULL,
    exercise_name VARCHAR(100) NOT NULL,
    sets INT,
    repetitions INT,
    duration_minutes INT,
    calories_burned INT,
    CONSTRAINT fk_workout_recommend_ex
        FOREIGN KEY (recommendation_id)
        REFERENCES workout_recommendations(id)
        ON DELETE CASCADE
);

CREATE TABLE workout_logs (
    id CHAR(36) PRIMARY KEY NOT NULL,
    user_id CHAR(36) NOT NULL,
    log_date DATE NOT NULL,
    source VARCHAR(20) NOT NULL,
    recommendation_id CHAR(36),
    total_minutes INT,
    total_calories_burned INT,
    created_at DATETIME(6) NOT NULL,
    UNIQUE KEY uq_workout_log_user_date (user_id, log_date),
    CONSTRAINT fk_workout_log_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_workout_log_recommend FOREIGN KEY (recommendation_id) REFERENCES workout_recommendations(id) ON DELETE SET NULL
);

CREATE TABLE workout_log_exercises (
    id CHAR(36) PRIMARY KEY NOT NULL,
    workout_log_id CHAR(36) NOT NULL,
    exercise_name VARCHAR(100) NOT NULL,
    sets INT,
    repetitions INT,
    duration_minutes INT,
    calories_burned INT,
    CONSTRAINT fk_workout_log_ex
        FOREIGN KEY (workout_log_id)
        REFERENCES workout_logs(id)
        ON DELETE CASCADE
);