ERD improvement

Tables (15)

chat_messages

  • id — BIGINT — Primary key
  • role — ENUM('USER', 'AI') — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • context_type — ENUM('TOTAL', 'PRODUCT', 'INGREDIENT', 'SKIN_TYPE') — 질문의 주요 컨텍스트
  • content — TEXT — NOT NULL
  • created_at — TIMESTAMP

ingredients

  • id — BIGINT — Primary key
  • korean_name — VARCHAR(255) — NOT NULL
  • english_name — VARCHAR(255)
  • description — TEXT

mbti_types (피부 MBTI 유형 정보)

  • mbti_id (id) — BIGINT — Primary key — NOT NULL
  • code (MBTI 코드) — CHAR(4) — NOT NULL — e.g., MOST, DOSP
  • name (MBTI 결과명) — VARCHAR(100) — NOT NULL — e.g., 고수분 고유분 민감성 탄력저하
  • mbti_skin_type (MBTI 유형이 대표하는 피부타입) — ENUM('건성, '지성', 수부지' ,'민감성', '복합성') — NOT NULL
  • description (상세 설명) — TEXT — NOT NULL

product_ingredients

  • ingredient_id — BIGINT — Foreign key → ingredients.id — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL

product_target_concerns

  • product_id — BIGINT — Foreign key → products.id — NOT NULL

products

  • id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • brand — VARCHAR(100)
  • category — ENUM('토너', '앰플', '세럼', '로션', '크림', '팩', '패드', '스킨') — NOT NULL
  • description — TEXT
  • image_url — VARCHAR(2048)
  • naver_product_id — VARCHAR(100) — 네이버 API 연동을 위한 ID

recommendation_log

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL — 추천/변경 제안된 제품
  • based_on_routine_group_id — BIGINT — Foreign key → routine_groups.id — NOT NULL
  • action_type — ENUM('ADD', 'REPLACE', 'REMOVE') — NOT NULL
  • reason — TEXT — AI가 추천한 이유
  • created_at — TIMESTAMP

routine_groups

  • id — BIGINT — Primary key
  • name — VARCHAR(100) — NOT NULL — e.g., 여름철 집중 케어 루틴
  • is_active — BOOLEAN — NOT NULL — 현재 사용자가 사용하는 메인 루틴인지
  • created_at — TIMESTAMP
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL

routine_items

  • id — BIGINT — Primary key — NOT NULL
  • routine_group_id — BIGINT — Foreign key → routine_groups.id — NOT NULL
  • time_of_day — ENUM('MORNING', 'NIGHT') — NOT NULL
  • step — INT — NOT NULL — 제품 바르는 순서 (1, 2, 3..)
  • custom_memo — TEXT — 사용자가 남기는 사용법 메모

skin_concerns (피부 고민)

  • concern_id (피부고민ID) — BIGINT — Primary key — NOT NULL
  • name (피부고민이름) — VARCHAR(100) — NOT NULL
  • description (고민설명) — TEXT — NOT NULL

social_accounts (소셜 계정)

  • soicial_id (소셜계정ID) — BIGINT — Primary key — NOT NULL
  • user_id (사용자ID) — BIGINT — Foreign key → users.user_id — NOT NULL
  • provider (소셜 로그인 제공자) — ENUM('NAVER', 'KAKAO', 'GOOGLE') — NOT NULL — UNIQUE
  • provider_user_id (각 소셜 서비스에서 제공하는 사용자의 고유 ID) — VARCHAR(255) — NOT NULL — UNIQUE
  • connected_at (소셜 계정 연결 시각) — TIMESTAMP — NOT NULL

user_profile (사용자 피부 정보)

  • profile_id (피부정보ID) — BIGINT — Primary key — NOT NULL
  • user_id (사용자ID) — BIGINT — Foreign key → users.user_id — NOT NULL — UNIQUE
  • mbti_id (피부 MBTI id) — BIGINT — Foreign key → mbti_types.mbti_id — NOT NULL
  • skin_type (피부타입) — ENUM('건성', '지성', '수부지', 민감성', '복합성') — NOT NULL
  • age_group (연령대) — BIGINT — NOT NULL
  • sensitivity_score (만감도 지수) — BIGINT — NOT NULL
  • oil_score (유분 지수) — BIGINT — NOT NULL
  • moisture_score (수분 지수) — BIGINT — NOT NULL
  • tension_score (탄력 지수) — BIGINT — NOT NULL
  • created_at (생성일자) — TIMESTAMP — NOT NULL
  • updated_at (수정일자) — TIMESTAMP

user_profile_log (사용자 피부 변화 로그)

  • log_id (로그ID) — BIGINT — Primary key — NOT NULL
  • profile_id (피부정보ID) — BIGINT — Foreign key → user_profile.profile_id — NOT NULL
  • user_skin_id (사용자피부고민ID) — BIGINT — Foreign key → user_skin_concerns.id — NOT NULL
  • user_type (피부타입) — ENUM('건성', '지성', '수부지', 민감성', '복합성') — NOT NULL
  • moisture_score (수분점수) — BIGINT — NOT NULL
  • oil_score_log (유분점수) — BIGINT — NOT NULL
  • sensitivity_score_log (민감도점수) — BIGINT — NOT NULL
  • tension_score_log (탄력점수) — BIGINT — NOT NULL
  • created_at (생성일자) — TIMESTAMP — NOT NULL

user_skin_concerns (피부 고민 - 사용자 피부 정보)

  • id (사용자피부고민ID) — BIGINT — Primary key — NOT NULL
  • concern_id (피부고민ID) — BIGINT — Foreign key → skin_concerns.concern_id — NOT NULL
  • profile_id (피부정보ID) — BIGINT — Foreign key → user_profile.profile_id — NOT NULL

users

  • user_id (사용자ID) — BIGINT — Primary key — NOT NULL
  • email (이메일) — VARCHAR(255) — UNIQUE
  • password_hash (비밀번호) — VARCHAR(255) — UNIQUE
  • nickname (닉네임) — VARCHAR(100) — NOT NULL
  • role (회원/관리자 확인) — ENUM('USER', 'ADMIN') — NOT NULL
  • state (회원 유무) — TINYINT — NOT NULL — 회원 1 / 탈퇴 0
  • created_at (생성일자) — TIMESTAMP
  • updated_at (수정일자) — TIMESTAMP
  • deleted_at (탈퇴일자) — TIMESTAMP