Final_pj_v3

Tables (10)

photo_features

  • feature_id — CHAR(36) — Primary key — NOT NULL
  • photo_id — CHAR(36) — Foreign key → room_photos.photo_id — NOT NULL
  • mood_main — VARCHAR(50)
  • tone — VARCHAR(50)
  • features_json — JSON
  • embedding_ref — VARCHAR(128)
  • embedding_model — VARCHAR(50)
  • confidence — DECIMAL(5,4)
  • model_version — VARCHAR(50)
  • created_at — DATETIME — NOT NULL

plants

  • plant_id — BIGINT — Primary key — NOT NULL
  • name_ko — VARCHAR(100) — NOT NULL
  • scientific_name — VARCHAR(150)
  • name_en — VARCHAR(120)
  • market_name — VARCHAR(120)
  • family_ko — VARCHAR(120)
  • origin — VARCHAR(120)
  • category — ENUM('관엽', '꽃', '열매', '다육', '선인장', '허브', '덩굴', '기타')
  • leaf_gloss — ENUM('없음', '약함', '있음')
  • evergreen — TINYINT(1) — 1=evergreen,0=not evergreen
  • leaf_pattern — ENUM('없음', '줄무늬', '반점', '마블', '테두리')
  • height_min_cm — SMALLINT
  • height_max_cm — SMALLINT
  • width_min_cm — SMALLINT
  • width_max_cm — SMALLINT
  • flower_season — VARCHAR(50) — 예: 봄;여름 또는 없음
  • flower_color — VARCHAR(60) — 예: 흰색;분홍 또는 없음
  • fruit_season — VARCHAR(50) — 예: 여름 또는 없음
  • fruit_color — VARCHAR(60) — 예: 빨강 또는 없음
  • fragrance_strength — ENUM('없음', '약함', '중간', '강함')
  • care_difficulty — ENUM('쉬움', '보통', '어려움')
  • care_demand — ENUM('낮음', '중간', '높음')
  • growth_speed — ENUM('느림', '보통', '빠름')
  • light_min — ENUM('저광', '중간광', '밝은간접광')
  • light_max — ENUM('저광', '중간광', '밝은간접광')
  • direct_sun — ENUM('불가', '주의', '가능')
  • window_distance — ENUM('0~50cm', '50~150cm', '150~300cm', '300cm+')
  • temp_min_c — TINYINT
  • temp_max_c — TINYINT
  • winter_min_c — TINYINT
  • humidity_pref — ENUM('건조OK', '보통', '고습선호')
  • fertilizer_need — ENUM('불필요', '가끔', '요구함')
  • soil_ph — ENUM('산성', '약산성', '중성', '약알칼리')
  • soil_drainage — ENUM('배수잘됨', '보통', '보습성')
  • watering_spring — ENUM('겉흙 마르면 듬뿍', '건조 후 물주기', '항상 촉촉(과습주의)'
  • 소량 — 자주')
  • watering_summer — ENUM('겉흙 마르면 듬뿍', '건조 후 물주기', '항상 촉촉(과습주의)'
  • 소량 — 자주')
  • watering_fall — ENUM('겉흙 마르면 듬뿍', '건조 후 물주기', '항상 촉촉(과습주의)'
  • 소량 — 자주')
  • watering_winter — ENUM('겉흙 마르면 듬뿍', '건조 후 물주기', '항상 촉촉(과습주의)'
  • 소량 — 자주')
  • human_allergy_caution — TINYINT(1) — 1=주의,0=아님
  • pet_caution — TINYINT(1) — 1=주의,0=아님
  • functional_info — VARCHAR(255)
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

reco_sessions

  • session_id — CHAR(36) — Primary key — NOT NULL
  • user_num — INT — Foreign key → users.user_num — NOT NULL
  • photo_id (photo_id2) — CHAR(36) — Foreign key → room_photos.photo_id — NOT NULL
  • session_kind — ENUM('plant_reco','spot_reco','plant_and_spot_reco') — NOT NULL
  • status — ENUM('created','photo_attached','spots_detected','light_analyzed', 'recommended','rendering','done','failed') — NOT NULL
  • algo_version — VARCHAR(50)
  • params_json — JSON
  • created_at — DATETIME — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • note — VARCHAR(512)

recommendation_items

  • rec_item_id — CHAR(36) — Primary key — NOT NULL
  • session_id — CHAR(36) — Foreign key → reco_sessions.session_id — NOT NULL
  • plant_id — BIGINT — Foreign key → plants.plant_id — NOT NULL
  • spot_id — CHAR(36) — Foreign key → room_spots.spot_id — NOT NULL
  • rank_no — INT — NOT NULL
  • score_total — DECIMAL(10,6)
  • score_style — DECIMAL(10,6)
  • score_survival — DECIMAL(10,6)
  • is_best — TINYINT(1) — NOT NULL
  • reason_json — JSON
  • kg_trace_json — JSON
  • created_at — DATETIME — NOT NULL

renders

  • render_id — CHAR(36) — Primary key — NOT NULL
  • session_id — CHAR(36) — Foreign key → reco_sessions.session_id — NOT NULL
  • spot_id — CHAR(36) — Foreign key → room_spots.spot_id — NOT NULL
  • plant_id — BIGINT — Foreign key → plants.plant_id — NOT NULL
  • image_url — VARCHAR(1024)
  • status — ENUM('queued','processing','done','failed') — NOT NULL
  • meta_json — JSON
  • error_msg — VARCHAR(512)
  • created_at — DATETIME — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

room_photos

  • photo_id — CHAR(36) — Primary key — NOT NULL
  • user_num — INT — Foreign key → users.user_num — NOT NULL
  • image_url — VARCHAR(1024) — NOT NULL
  • width_px — INT
  • height_px — INT
  • created_at — DATETIME — NOT NULL

room_spots

  • spot_id — CHAR(36) — Primary key — NOT NULL
  • photo_id — CHAR(36) — Foreign key → room_photos.photo_id — NOT NULL
  • spot_index — INT — NOT NULL
  • bbox_x — INT
  • bbox_y — INT
  • bbox_w — INT
  • bbox_h — INT
  • polygon — JSON
  • created_at — DATETIME — NOT NULL

spot_light_conditions

  • light_id — CHAR(36) — Primary key — NOT NULL
  • spot_id — CHAR(36) — Foreign key → room_spots.spot_id — NOT NULL
  • light_level — ENUM('저광','중간광','반그늘','직사광가능','직사광주의')
  • window_distance_band — ENUM('0~50cm','50~150cm','150~300cm','300cm+')
  • confidence — DECIMAL(5,4)
  • model_version — VARCHAR(50)
  • measured_at — DATETIME — NOT NULL

user_profile

  • user_num — INT — Foreign key → users.user_num — NOT NULL
  • has_pet — TINYINT(1) — NOT NULL
  • has_kid — TINYINT(1) — NOT NULL
  • allergy_sensitive — TINYINT(1) — NOT NULL
  • skill_level — ENUM('초보','중급','고급')
  • watering_habit — ENUM('잊음','보통','자주')
  • profile_extra — JSON
  • created_at — DATETIME — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

users

  • user_num — INT — Primary key — NOT NULL
  • user_id — VARCHAR(20) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • gender (gedner)
  • birthdate — VARCHAR(20)
  • phone — VARCHAR(20)
  • email — VARCHAR(50)
  • created_at — DATETIME — NOT NULL