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)