7차과제_ERD
테이블 (13)
goal (Goal)
- goal_id (Goal ID) — INT — 기본 키 — NOT NULL
- goal_name (Goal Name) — VARCHAR(50)
item (Item)
- item_id (Item ID) — INT — 기본 키 — NOT NULL
- goal_id (ID2) — INT — 외래 키 → goal.goal_id — NOT NULL
- product_id (Product ID) — INT — 외래 키 → product.product_id — NOT NULL
- product_status_id (Product Status ID) — INT — 외래 키 → product.product_status_id — NOT NULL
- item_name (Name) — VARCHAR(50)
order_details (Order Details)
- order_id (Order ID) — INT — 외래 키 → user_order_log.order_id — NOT NULL
- user_id (User ID) — INT — 외래 키 → user_order_log.user_id — NOT NULL
- product_id (Product ID) — INT — 외래 키 → product.product_id — NOT NULL
- product_status_id (Product Status ID) — INT — 외래 키 → product.product_status_id — NOT NULL
- order_quantity (Ordered Quantity) — INT — NOT NULL
- order_unit price (Ordered Unit Price) — INT — NOT NULL
product (Product)
- product_id (Product ID) — INT — 기본 키 — NOT NULL
- product_status_id (Product Status ID) — INT — 외래 키 → product_status.product_status_id
- goal_id (ID2) — INT — 외래 키 → goal.goal_id — NOT NULL
- product_name (Product Name) — VARCHAR(50)
- product_price (Product Price) — INT
- is_bundle (Bundle Product) — BOOLEAN — NOT NULL
product_status (Product Status Code)
- product_status_id (Product Status ID) — INT — 기본 키 — NOT NULL
- product_status_name (Product Status Name) — VARCHAR(50) — NOT NULL
recommended_mapping (Recommended Mapping)
- mapping_id (Mapping ID) — INT — 기본 키 — NOT NULL
- product_id (Product ID) — INT — 외래 키 → product.product_id — NOT NULL
- segment_id (Segment ID) — INT — 외래 키 → segment_master.segment_id — NOT NULL
- product_status_id (Product Status ID) — INT — 외래 키 → product.product_status_id — NOT NULL
- weight_type (Weight Type) — VARCHAR(50) — BASE/CONTENT/COLLAB
- weight_value (Weight Value) — INT
recommended_result (Recommended Result)
- result_id (Result ID) — INT — 기본 키 — NOT NULL
- user_id (User ID) — INT — 외래 키 → user_master.user_id — NOT NULL — On Delete Cascade
- product_id (Product ID) — INT — 외래 키 → product.product_id — NOT NULL
- product_status_id (Product Status ID) — INT — 외래 키 → product.product_status_id — NOT NULL
- base_weight (Base Weight) — INT
- content_weight (Content Weight) — INT
- collab_weight (Collab Weight) — INT
- biz_weight (Business Weight) — INT
- total_weight (Total Weight) — INT — bw + conw + colw + bizw
segment_master (Segment Master)
- segment_id (Segment ID) — INT — 기본 키 — NOT NULL
- segment_name (Segment Name) — VARCHAR(50) — NOT NULL
user_game_log (Game Log)
- user_id (User ID) — INT — 외래 키 → user_master.user_id — NOT NULL
- user_level (Level) — INT — 보유한 캐릭터 중 최대 레벨
- user_tpt (Total Playtime) — INT — 총 플레이타임
- user_tad (Total Attack & Defense) — INT — 최대공방합
- user_mlsm (Life Skill Mastery) — INT — 생활 카테고리 중 가장 높은 숙련도
user_login_log (Log-in Log)
- user_login_log (Key) — INT — 기본 키 — NOT NULL
- user_id (User ID) — INT — 외래 키 → user_master.user_id — NOT NULL
- login_time (Log-in Time) — DATETIME — NOT NULL
- ip_adress (IP Adress) — VARCHAR(50) — NOT NULL
- device_type (Device Type) — VARCHAR(50)
user_master (User Master)
- user_id (User ID) — INT — 기본 키 — NOT NULL
- user_email (User Email) — VARCHAR(50) — NOT NULL
- user_password (User Password) — VARCHAR(50) — NOT NULL
- user_name (User Name) — VARCHAR(50) — NOT NULL
- user_signup_date (User Sign-up Date) — DATE — NOT NULL
user_order_log (Order Log)
- order_id (Order ID) — INT — 기본 키 — NOT NULL
- user_id (User ID) — INT — 외래 키 → user_master.user_id — NOT NULL
- order_date (Order Date) — DATE — NOT NULL
- order_type (Order Type) — VARCHAR(50) — Acoin / 펄
- order_price (Order Price) — INT — NOT NULL
user_stats (User Stats)
- user_id (User ID) — INT — 외래 키 → user_master.user_id — NOT NULL
- segment_id (Segment ID) — INT — 외래 키 → segment_master.segment_id — NOT NULL
- user_apt (Average Playtime) — INT — 평균 플레이타임(월/단위: 시간)
- user_apf (Average Play Frequency) — INT — 평균 웹사이트/게임 접속 횟수(월/단위: 회)
- user_engagement (User Engagement) — FLOAT — NOT NULL — 평균 플레이타임(월/단위: 시간) x 평균 접속 횟수(월/단위: 회)
- user_playstyle (Play Style) — FLOAT — NOT NULL — (최대 공방합 / 800) / (주력 생활 숙련도 / 2000)