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