Lazada Products ERD

Tables (21)

brands (브랜드 (brands))

  • brand_id — INT — Primary key — NOT NULL
  • brand_name — VARCHAR(100) — NOT NULL
  • brand_type — VARCHAR(50)
  • country — VARCHAR(50)

categories (카테고리 (categories))

  • category_id (카테고리 id (category_id)) — INT — Primary key — NOT NULL
  • category_name — VARCHAR(255) — NOT NULL
  • parent_id — INT — Foreign key → categories.category_id
  • depth (카테고리 깊이(단계) (depth)) — INT

data_anomalies

  • anomaly_id — INT — Primary key — NOT NULL
  • raw_data_id — INT — Foreign key → raw_data.id
  • sku — VARCHAR(100)
  • column_name — VARCHAR(100)
  • anomaly_type — VARCHAR(100)
  • original_value — TEXT
  • cleaned_value — TEXT
  • action_taken — VARCHAR(100)
  • detected_at — DATETIME

data_cleaning_log

  • log_id — INT — Primary key — NOT NULL
  • step_order — INT
  • step_name — VARCHAR(100)
  • description — TEXT
  • affected_rows — INT
  • before_count — INT
  • after_count — INT
  • sql_query — TEXT
  • executed_at — DATETIME

members (회원 (members))

  • member_id — INT — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100)
  • phone — VARCHAR(20)
  • address — TEXT
  • joined_at — DATETIME
  • is_active — BOOLEAN

order_items (주문 상세 (order_items))

  • id — INT — Primary key — NOT NULL
  • order_id — INT — Foreign key → orders.order_id
  • sku (상품 고유코드 (sku)) — VARCHAR(100) — Foreign key → products.sku
  • quantity — INT
  • unit_price — DECIMAL(15,2)
  • variation_id — INT — Foreign key → product_variations.variation_id

orders (주문 (orders))

  • order_id (주문 id (order_id)) — INT — Primary key — NOT NULL
  • member_id (회원 id (member_id)) — INT — Foreign key → members.member_id
  • total_price — DECIMAL(20,2)
  • currency — VARCHAR(10)
  • status — VARCHAR(50)
  • shipping_address (배송 주소 (shipping_address)) — TEXT
  • created_at — DATETIME

payments (결제 (payments))

  • payment_id — INT — Primary key — NOT NULL
  • order_id — INT — Foreign key → orders.order_id
  • amount — DECIMAL(20,2)
  • currency — VARCHAR(10)
  • method — VARCHAR(50)
  • status — VARCHAR(50)
  • paid_at — DATETIME

product_categories (상품 / 카테고리 연결 (product_categories))

  • id — INT — Primary key — NOT NULL
  • sku (상품 고유코드 (sku)) — VARCHAR(100) — Foreign key → products.sku
  • category_id — INT — Foreign key → categories.category_id
  • depth (카테고리 깊이(단계) (depth)) — INT

product_colors (상품 색상 (product_colors))

  • color_id — INT — Primary key — NOT NULL
  • sku (상품 고유코드 (sku)) — VARCHAR(100) — Foreign key → products.sku
  • color_name — VARCHAR(100)

product_images (상품 이미지 (product_images))

  • image_id — INT — Primary key — NOT NULL
  • sku (상품 고유코드 (sku)) — VARCHAR(100) — Foreign key → products.sku
  • image_url — TEXT — NOT NULL
  • size_type — VARCHAR(50)
  • display_order — INT

product_specs (상품 사양(product_specs))

  • spec_id — INT — Primary key — NOT NULL
  • sku (상품 고유코드 (sku)) — VARCHAR(100) — Foreign key → products.sku
  • spec_name — VARCHAR(255)
  • spec_value — TEXT

product_variations (상품 변형 옵션 (product_variations))

  • variation_id — INT — Primary key — NOT NULL
  • sku (상품 고유코드 (sku)) — VARCHAR(100) — Foreign key → products.sku
  • variation_name — VARCHAR(255)
  • variation_value — VARCHAR(255)
  • is_selected — BOOLEAN

products (상품 (products))

  • sku (상품 고유코드 (sku)) — VARCHAR(100) — Primary key — NOT NULL
  • mpn (제조사 부품번호 (mpn)) — VARCHAR(100)
  • title — TEXT — NOT NULL
  • product_url (상품 url (product_url)) — TEXT
  • domain — VARCHAR(100)
  • brand_id — INT — Foreign key → brands.brand_id
  • seller_id — INT — Foreign key → sellers.seller_id
  • currency — VARCHAR(10)
  • initial_price — DECIMAL(15,2)
  • final_price — DECIMAL(15,2)
  • discount_rate — DECIMAL(5,2)
  • number_sold — INT
  • gmv (총거래액 (gmv)) — DECIMAL(20,2)
  • rating — DECIMAL(3,2)
  • review_count (리뷰 수 (review_count)) — INT
  • product_description — TEXT
  • is_lazmall (라자다몰 입점여부 (is_lazmall)) — BOOLEAN
  • is_super_seller (슈퍼셀러 여부 (is_super_seller)) — BOOLEAN

promotions (할인 (promotions))

  • promo_id — INT — Primary key — NOT NULL
  • sku (상품 고유코드 (sku)) — VARCHAR(100) — Foreign key → products.sku
  • promotion_text (할인 텍스트 (promotion_text)) — VARCHAR(500)

raw_data

  • id — INT — Primary key — NOT NULL
  • url — TEXT
  • title — TEXT
  • rating — VARCHAR(20)
  • reviews — VARCHAR(20)
  • initial_price — VARCHAR(50)
  • final_price — VARCHAR(50)
  • currency — VARCHAR(10)
  • image — TEXT
  • seller_name — VARCHAR(255)
  • breadcrumb — TEXT
  • product_specifications — TEXT
  • product_description — TEXT
  • seller_ratings — VARCHAR(20)
  • seller_ship_on_time — VARCHAR(20)
  • seller_chat_response — VARCHAR(20)
  • sku — VARCHAR(100)
  • mpn — VARCHAR(100)
  • colors — TEXT
  • variations — TEXT
  • color — VARCHAR(100)
  • returns_and_warranty — TEXT
  • is_super_seller — TEXT
  • promotions — TEXT
  • brand_raw — TEXT
  • product_variation — TEXT
  • lazmall — TEXT
  • domain — VARCHAR(100)
  • number_sold — VARCHAR(50)
  • gmv — VARCHAR(50)
  • import_batch — INT
  • imported_at — DATETIME

returns_warranty (반품 / 보증 정책 (returns_warranty))

  • rw_id — INT — Primary key — NOT NULL
  • sku (상품 고유코드 (sku)) — VARCHAR(100) — Foreign key → products.sku
  • policy_text (정책 내용 (policy_text)) — VARCHAR(500)

reviews (리뷰 (reviews))

  • review_id — INT — Primary key — NOT NULL
  • sku — VARCHAR(100) — Foreign key → products.sku
  • member_id — INT
  • rating — DECIMAL(3,2)
  • review_text — TEXT
  • created_at — DATETIME

sellers (판매자 (sellers))

  • seller_id — INT — Primary key — NOT NULL
  • seller_name — VARCHAR(255) — NOT NULL
  • seller_rating — DECIMAL(3,2)
  • ship_on_time_pct — VARCHAR(10)
  • chat_response_pct — VARCHAR(10)
  • is_super_seller — BOOLEAN
  • lazmall — BOOLEAN
  • domain — VARCHAR(100)

shipping (배송 (shipping))

  • ship_id — INT — Primary key — NOT NULL
  • order_id — INT — Foreign key → orders.order_id
  • tracking_no — VARCHAR(100)
  • carrier — VARCHAR(100)
  • status — VARCHAR(50)
  • shipped_at — DATETIME
  • delivered_at — DATETIME

장바구니 (cart_items)

  • cart_id — INT — Primary key — NOT NULL
  • member_id — INT — Foreign key → members.member_id
  • sku (상품 고유코드 (sku)) — VARCHAR(100) — Foreign key → products.sku
  • quantity — INT
  • variation_id (선택 옵션 번호 (variation_id)) — INT — Foreign key → product_variations.variation_id
  • added_at — DATETIME