데이터베이스 과제

테이블 (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