2Z-SSAFIT-241117

Tables (16)

canteen

  • canteen_id — INT — Primary key — NOT NULL — 식당id
  • canteen_name — VARCHAR(100) — NOT NULL — 식당이름
  • created_at — TIMESTAMP — NOT NULL — 생성일자(YYYY-MM-DD)
  • updated_at — TIMESTAMP — NOT NULL — 최종업데이트일자(YYYY-MM-DD)

canteen_menu

  • menu_id — INT — Primary key — NOT NULL — 메뉴id(auto increment)
  • canteen_id — INT — Foreign key → canteen.canteen_id — NOT NULL — 식당id
  • menu_name — VARCHAR(100) — NOT NULL — 메뉴이름
  • calories — INT — NOT NULL — 칼로리(1인분)
  • Field (menu_img) — VARCHAR(255) — 메뉴사진(NULL 이면 default 사진 )
  • created_at — TIMESTAMP — NOT NULL — 생성일자(YYYY-MM-DD)
  • updated_at — TIMESTAMP — NOT NULL — 최종업데이트일자(YYYY-MM-DD)

canteen_schedule

  • schedule_id — INT — Primary key — NOT NULL — 메뉴 스케줄 id
  • menu_id — INT — Foreign key → canteen_menu.menu_id — NOT NULL — 메뉴id(auto increment)
  • meal_date — TIMESTAMP — NOT NULL — 날짜(YYYY-MM-DD)
  • meal_time — VARCHAR(10) — NOT NULL — 식사시간(breakfast, lunch, dinner, snack)
  • meal_kind — INT — NOT NULL — 당일식단번호 (ex 웰스토리1,2, 도시주방3,4,5)
  • main_yn — VARCHAR(1) — NOT NULL — 메인메뉴여부(Y/N)
  • created_at — TIMESTAMP — NOT NULL — 생성일자(YYYY-MM-DD)
  • updated_at — TIMESTAMP — NOT NULL — 최종업데이트일자(YYYY-MM-DD)

canteen_user_vote

  • user_vote_id — INT — Primary key — NOT NULL — 사용자투표id
  • user_id — VARCHAR(10) — Foreign key → users.user_id — NOT NULL — 사번/학번
  • vote_id — INT — Foreign key → canteen_vote.vote_id — NOT NULL — 투표 id
  • option_id — INT — Foreign key → canteen_vote_option.option_id — NOT NULL — 투표 옵션 id
  • created_at — TIMESTAMP — NOT NULL — 투표 시간

canteen_vote

  • vote_id — INT — Primary key — NOT NULL — 투표 id
  • start_date — TIMESTAMP — NOT NULL — 투표 시작일
  • end_date — TIMESTAMP — NOT NULL — 투표 종료일
  • created_at — TIMESTAMP — NOT NULL — 생성일자(YYYY-MM-DD)
  • updated_at — TIMESTAMP — NOT NULL — 최종업데이트일자(YYYY-MM-DD)

canteen_vote_option

  • option_id — INT — Primary key — NOT NULL — 투표 옵션 id
  • vote_id — INT — Foreign key → canteen_vote.vote_id — NOT NULL — 투표 id
  • schedule_id — INT — Foreign key → canteen_schedule.schedule_id — NOT NULL — 메뉴 스케줄 id
  • option_count — INT — NOT NULL — 해당 옵션 투표 수

eating_record

  • eating_id — INT — Primary key — NOT NULL — 식사기록id(auto increment)-메인메뉴
  • user_id — VARCHAR(10) — Foreign key → users.user_id — NOT NULL — 사번/학번
  • schedule_id — INT — Foreign key → canteen_schedule.schedule_id — 메뉴 스케줄 id
  • food_id — INT — Foreign key → food.food_id — 푸드id(auto increment)
  • custom_food_name — VARCHAR(100) — 식사메뉴이름 (직접입력시)
  • main_yn — VARCHAR(1) — NOT NULL — 메인메뉴여부(Y/N) - 기록표기
  • actual_quantity — INT — 실제 섭취량
  • calories_get — FLOAT — NOT NULL — 섭취 칼로리량
  • meal_date — TIMESTAMP — NOT NULL — 식사날짜(YYYY-MM-DD)
  • meal_time — VARCHAR(10) — NOT NULL — 식사시간(breakfast, lunch, dinner, snack)
  • created_at — TIMESTAMP — NOT NULL — 생성일자(YYYY-MM-DD)
  • updated_at — TIMESTAMP — NOT NULL — 최종업데이트일자(YYYY-MM-DD)

exercise

  • exercise_id — INT — Primary key — NOT NULL — 운동id
  • exercise_2nd_cate_id — INT — Foreign key → exercise_2nd_category.exercise_2nd_cate_id — NOT NULL — 운동 소분류id(2차카테고리)
  • exercise_name — VARCHAR(100) — NOT NULL — 운동 이름
  • calories_per_min_per_kg (met) — DECIMAL(5,2) — 1met = 3.5ml*무게*시간 (산소소모량-1met>5kcal), NULL 기타
  • created_at — TIMESTAMP — NOT NULL — 생성일자(YYYY-MM-DD)
  • updated_at — TIMESTAMP — NOT NULL — 최종업데이트일자(YYYY-MM-DD)

exercise_1st_category

  • exercise_1st_cate_id — INT — Primary key — NOT NULL — 운동 대분류id(1차카테고리)
  • exercise_1st_cate_name — VARCHAR(100) — NOT NULL — 운동 대분류명
  • created_at — TIMESTAMP — NOT NULL — 생성일자(YYYY-MM-DD)
  • updated_at — TIMESTAMP — NOT NULL — 최종업데이트일자(YYYY-MM-DD)

exercise_2nd_category

  • exercise_2nd_cate_id — INT — Primary key — NOT NULL — 운동 소분류id(2차카테고리)
  • exercise_1st_cate_id — INT — Foreign key → exercise_1st_category.exercise_1st_cate_id — NOT NULL — 운동 대분류id(1차카테고리)
  • exercise_2nd_cate_name — VARCHAR(100) — NOT NULL — 운동 소분류명
  • created_at — TIMESTAMP — NOT NULL — 생성일자(YYYY-MM-DD)
  • updated_at — TIMESTAMP — NOT NULL — 최종업데이트일자(YYYY-MM-DD)

exercise_record

  • record_id — INT — Primary key — NOT NULL — 운동기록id
  • user_id — VARCHAR(10) — Foreign key → users.user_id — NOT NULL — 사번/학번
  • exercise_id — INT — Foreign key → exercise.exercise_id — NOT NULL — 운동id
  • custom_exercise_name — VARCHAR(100) — 기타 (직접 입력 운동 이름)
  • duration_minutes — INT — NOT NULL — 운동시간(분)
  • weight — DECIMAL(5,2) — 사용자 몸무게(이때)
  • bmr — DECIMAL(6,2) — 사용자 기초대사량(이때)
  • calories_burned — FLOAT — NOT NULL — 소모 칼로리
  • exercise_date — TIMESTAMP — NOT NULL — 운동일자
  • created_at — TIMESTAMP — NOT NULL — 생성일자(YYYY-MM-DD)
  • updated_at — TIMESTAMP — NOT NULL — 최종업데이트일자(YYYY-MM-DD)

food

  • food_id — INT — Primary key — NOT NULL — 푸드id(auto increment)
  • food_2nd_cate_id — INT — Foreign key → food_2nd_category.food_2nd_cate_id — NOT NULL — 식품중분류id(0:기타)
  • food_name — VARCHAR(100) — NOT NULL — 푸드 이름
  • calories — INT — 칼로리(100g 당), NULL 기타
  • base_quantity — INT — 기준량(단위 g/ml), NULL 기타
  • created_at — TIMESTAMP — NOT NULL — 생성일자(YYYY-MM-DD)
  • updated_at — TIMESTAMP — NOT NULL — 최종업데이트일자(YYYY-MM-DD)

food_1st_category

  • food_1st_cate_id — INT — Primary key — NOT NULL — 식품대분류id(0:기타)
  • food_1st_cate_name — VARCHAR(100) — NOT NULL — 식품대분류명
  • created_at — TIMESTAMP — NOT NULL — 생성일자(YYYY-MM-DD)
  • updated_at — TIMESTAMP — NOT NULL — 최종업데이트일자(YYYY-MM-DD)

food_2nd_category

  • food_2nd_cate_id — INT — Primary key — NOT NULL — 식품중분류id(0:기타)
  • food_1st_cate_id — INT — Foreign key → food_1st_category.food_1st_cate_id — NOT NULL — 식품대분류id(0:기타)
  • food_2nd_cate_name — VARCHAR(100) — NOT NULL — 푸드 카테고리명
  • created_at — TIMESTAMP — NOT NULL — 생성일자(YYYY-MM-DD)
  • updated_at — TIMESTAMP — NOT NULL — 최종업데이트일자(YYYY-MM-DD)

menu_review

  • review_id — INT — Primary key — NOT NULL — 리뷰id(auto increment)
  • eating_id — INT — Foreign key → eating_record.eating_id — NOT NULL — 식사기록id(auto increment)
  • rating — TINYINT — NOT NULL — 별점(1~5점)
  • comment — VARCHAR(255) — NOT NULL — 리뷰 내용
  • img_path — VARCHAR(255) — 리뷰 이미지 경로
  • created_at — TIMESTAMP — 생성일자(YYYY-MM-DD)
  • updated_at — TIMESTAMP — 최종업데이트일자(YYYY-MM-DD)

users

  • user_id — VARCHAR(10) — Primary key — NOT NULL — 사번/학번
  • password — VARCHAR(50) — NOT NULL — 비밀번호
  • user_name — VARCHAR(100) — NOT NULL — 이름
  • email — VARCHAR(320) — 이메일(unique)
  • gender — VARCHAR(10) — NOT NULL — 성별(male, female)
  • birth — DATE — NOT NULL — 생년월일(YYYY-MM-DD)
  • height — DECIMAL(5,2) — 신체-키(단위cm)
  • weight — DECIMAL(5,2) — 몸무게(단위kg)
  • bmr — DECIMAL(6,2) — 기초대사량(단위kcal/day)
  • join_date — TIMESTAMP — NOT NULL — 가입일자(YYYY-MM-DD)
  • updated_at — TIMESTAMP — NOT NULL — 최종업데이트일자(YYYY-MM-DD)