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)