데이터베이스 과제
테이블 (7)
DAILY_SUMMARIES
- summary_id — INT
- user_id — INT — 외래 키 → USERS.user_id — NOT NULL
- summary_date — DATE — NOT NULL
- total_calories — DECIMAL(7,1)
- total_carbs_g — DECIMAL(6,1)
- total_protein_g — DECIMAL(6,1)
- total_fat_g — DECIMAL(6,1)
- goal_achieve_pct — DECIMAL(5,1)
FOOD_ITEMS
- food_id — INT
- food_name — VARCHAR(100) — NOT NULL
- cal_per_100g — DECIMAL(6,1) — NOT NULL
- carbs_per_100g — DECIMAL(5,1)
- protein_per_100g — DECIMAL(5,1)
- fat_per_100g — DECIMAL(5,1)
- category — VARCHAR(50)
MEAL_FOOD_ITEMS
- item_id — INT
- log_id — INT — 외래 키 → MEAL_LOGS.log_id — NOT NULL
- food_id — INT — 외래 키 → FOOD_ITEMS.food_id — NOT NULL
- amount_g — DECIMAL(6,1) — NOT NULL
- calories — DECIMAL(6,1)
- carbs_g — DECIMAL(5,1)
- protein_g — DECIMAL(5,1)
- fat_g — DECIMAL(5,1)
MEAL_LOGS
- log_id — INT
- user_id — INT — 외래 키 → USERS.user_id — NOT NULL
- log_date — DATE — NOT NULL
- meal_type — ENUM('breakfast','lunch','dinner','snack') — NOT NULL
- photo_url — VARCHAR(300)
- ai_result_json — TEXT
- created_at — DATETIME
NUTRITION_GOALS
- goal_id — INT
- user_id — INT — 외래 키 → USERS.user_id — NOT NULL
- goal_type — ENUM('lose','maintain','gain') — NOT NULL
- target_calories — INT — NOT NULL
- target_carbs_g — DECIMAL(6,1)
- target_protein_g — DECIMAL(6,1)
- target_fat_g — DECIMAL(6,1)
- is_active — TINYINT(1)
- created_at — DATETIME
USERS
- user_id — INT
- email — VARCHAR(100) — NOT NULL
- password_hash — VARCHAR(255) — NOT NULL
- name — VARCHAR(50) — NOT NULL
- birth_date — DATE
- gender — ENUM('M','F','OTHER')
- height_cm — DECIMAL(5,1)
- created_at — DATETIME
WEIGHT_RECORDS
- record_id — INT
- user_id — INT — 외래 키 → USERS.user_id — NOT NULL
- weight_kg — DECIMAL(5,1) — NOT NULL
- recorded_at — DATE — NOT NULL