farmingfamily

Tables (15)

care_activity_types

  • activity_type_id (관리활동 아이디) — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • description — TEXT
  • default_frequency_days — INT
  • icon — VARCHAR(50)
  • created_at — TIMESTAMP

care_records

  • record_id (관리기록아이디) — BIGINT — Primary key — NOT NULL
  • plant_id (작물 아이디) — BIGINT — Foreign key → user_plants.plant_id — NOT NULL
  • activity_type_id (관리활동 아이디) — BIGINT — Foreign key → care_activity_types.activity_type_id — NOT NULL
  • performed_date — TIMESTAMP
  • notes — TEXT
  • amount — VARCHAR(50)
  • next_due_date — DATE
  • is_completed — BOOLEAN
  • created_at — TIMESTAMP

diagnosis_images

  • image_id (이미지 아이디) — BIGINT — Primary key — NOT NULL
  • diagnosis_id (진단아이디) — BIGINT — Foreign key → plant_diagnoses.diagnosis_id — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • image_type — ENUM('symptom', 'full_plant', 'leaf', 'stem', 'root', 'flower') — NOT NULL
  • description — TEXT
  • uploaded_at — TIMESTAMP

disease_types

  • disease_id (병명 아이디) — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • category — ENUM('fungal', 'bacterial', 'viral', 'pest', 'nutrient', 'environmental') — NOT NULL
  • symptoms — TEXT
  • treatment — TEXT
  • prevention — TEXT
  • severity — ENUM('low', 'medium', 'high', 'critical')
  • created_at — TIMESTAMP

expert_consultations

  • consultation_id — BIGINT
  • user_id — BIGINT — NOT NULL
  • plant_id — BIGINT — NOT NULL
  • expert_id — BIGINT
  • question — TEXT — NOT NULL
  • answer — TEXT
  • status — ENUM('pending', 'answered', 'closed')
  • priority — ENUM('low', 'medium', 'high')
  • created_at — TIMESTAMP
  • answered_at — TIMESTAMP

gardens

  • garden_id (토지 아이디) — BIGINT — Primary key — NOT NULL
  • user_id (유저아이디) — BIGINT — Foreign key → users.user_id — NOT NULL
  • user_id — BIGINT — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • location — VARCHAR(200)
  • size_sqm — DECIMAL(10,2)
  • garden_type — ENUM('indoor', 'outdoor', 'greenhouse', 'balcony') — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

growth_records

  • growth_id (성장기록 아이디) — BIGINT — Primary key — NOT NULL
  • plant_id (작물 아이디) — BIGINT — Foreign key → user_plants.plant_id — NOT NULL
  • height_cm — DECIMAL(8,2)
  • width_cm — DECIMAL(8,2)
  • leaf_count — INT
  • flower_count — INT
  • fruit_count — INT
  • health_score — DECIMAL(3,1)
  • notes — TEXT
  • photo_url — VARCHAR(500)
  • recorded_date — DATE — NOT NULL
  • created_at — TIMESTAMP

notifications

  • notification_id (알림 아이디) — BIGINT — Primary key — NOT NULL
  • user_id (유저아이디) — BIGINT — Foreign key → users.user_id — NOT NULL
  • plant_id (작물 아이디) — BIGINT — Foreign key → user_plants.plant_id — NOT NULL
  • type — ENUM('watering', 'fertilizing', 'disease_alert', 'general') — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • message — TEXT — NOT NULL
  • is_read — BOOLEAN
  • scheduled_for — TIMESTAMP
  • created_at — TIMESTAMP

plant_categories

  • categories_id (카테고리 아이디) — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • description — TEXT
  • created_at — TIMESTAMP

plant_diagnoses

  • diagnosis_id (진단아이디) — BIGINT — Primary key — NOT NULL
  • plant_id (작물 아이디) — BIGINT — Foreign key → user_plants.plant_id — NOT NULL
  • disease_id (병명 아이디) — BIGINT — Foreign key → disease_types.disease_id — NOT NULL
  • symptoms_description — TEXT — NOT NULL
  • diagnosis_result — TEXT
  • confidence_score — DECIMAL(5,2)
  • diagnosis_method — ENUM('ai', 'expert', 'self')
  • diagnosis_date — TIMESTAMP
  • status — ENUM('pending', 'confirmed', 'treated', 'resolved')
  • notes — TEXT

plant_diaries

  • diary_id (일지 아이디) — BIGINT — Primary key — NOT NULL
  • plant_id (작물 아이디) — BIGINT — Foreign key → user_plants.plant_id — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • content — TEXT — NOT NULL
  • mood — ENUM('excellent', 'good', 'fair', 'poor', 'critical')
  • weather — VARCHAR(50)
  • photo_url — VARCHAR(500)
  • entry_date — DATE — NOT NULL
  • created_at — TIMESTAMP

plant_species

  • species_id (종류 아이디) — BIGINT — Primary key — NOT NULL
  • categories_id (카테고리 아이디) — BIGINT — Foreign key → plant_categories.categories_id — NOT NULL
  • scientific_name — VARCHAR(150) — NOT NULL
  • common_name — VARCHAR(100) — NOT NULL
  • care_difficulty — ENUM('easy', 'medium', 'hard')
  • water_frequency_days — INT
  • light_requirement — ENUM('low', 'medium', 'high')
  • temperature_min — INT
  • temperature_max — INT
  • humidity_min — INT
  • humidity_max — INT
  • growth_season — ENUM('spring', 'summer', 'fall', 'winter', 'year_round')
  • description — TEXT
  • care_tips — TEXT
  • created_at — TIMESTAMP

sensor_data

  • sensor_id (센서아이디) — BIGINT — Primary key — NOT NULL
  • garden_id (토지 아이디) — BIGINT — Foreign key → gardens.garden_id — NOT NULL
  • temperature — DECIMAL(5,2)
  • humidity — DECIMAL(5,2)
  • light_intensity — DECIMAL(10,2)
  • soil_moisture — DECIMAL(5,2)
  • ph_level — DECIMAL(4,2)
  • recorded_at — TIMESTAMP

user_plants

  • plant_id (작물 아이디) — BIGINT — Primary key — NOT NULL
  • garden_id (토지 아이디) — BIGINT — Foreign key → gardens.garden_id — NOT NULL
  • user_id (유저아이디) — BIGINT — Foreign key → users.user_id — NOT NULL
  • species_id (종류 아이디) — BIGINT — Foreign key → plant_species.species_id — NOT NULL
  • nickname — VARCHAR(100)
  • planted_date — DATE
  • location_in_garden — VARCHAR(100)
  • notes — TEXT
  • is_healthy — BOOLEAN
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

users

  • user_id (유저아이디) — BIGINT — Primary key — NOT NULL
  • username — VARCHAR(50) — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(20)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • is_active — BOOLEAN