new erd

테이블 (24)

BIZ_COMMERCIAL_INFO

  • commercial_id — varchar(20)
  • company_id — varchar(20) — 외래 키 → BIZ_COMPANY_INFO.company_id
  • commercial_name — varchar(50)
  • url — varchar(300)
  • description — varchar(500)
  • use_yn — bool
  • create_id — varchar(20)
  • create_time — datetime
  • update_id — varchar(20)
  • update_time — datetime

BIZ_COMMERCIAL_SCHEDULE

  • panel_id — varchar(20) — 기본 키 — NOT NULL
  • seq — int(10) — 기본 키 — NOT NULL
  • kiosk_id — varchar(20) — 외래 키 → BIZ_KIOSK_INFO.kiosk_id — NOT NULL
  • commercial_id — varchar(20) — 외래 키 → BIZ_COMMERCIAL_INFO.commercial_id
  • start_date — date
  • end_date — date
  • create_id — varchar(20)
  • create_time — datetime
  • update_id — varchar(20)
  • update_time — datetime

BIZ_COMPANY_INFO

  • company_id — varchar(20)
  • company_name — varchar(50)
  • biz_id — varchar(20)
  • ceo_name — varchar(20)
  • tel_no — varchar(20)
  • email — varchar(50)
  • advertiser_yn — bool
  • suplier_yn — bool
  • use_yn — bool
  • create_id — varchar(20)
  • create_time — datetime
  • update_id — varchar(20)
  • update_time — datetime

BIZ_KIOSK_INFO

  • kiosk_id — varchar(20) — 기본 키 — NOT NULL
  • store_id — varchar(20) — 외래 키 → BIZ_STORE_INFO.store_id
  • kiosk_name — varchar(30)
  • ip — varchar(30)
  • display_type — varchar(5)
  • version — varchar(30)
  • status — varchar(2)
  • open_time — time
  • close_time — time
  • create_id — varchar(20)
  • create_time — datetime
  • update_id — varchar(20)
  • update_time — datetime

BIZ_MACHINE_INFO

  • machine_id — varchar(20) — 기본 키 — NOT NULL
  • kiosk_id — varchar(20) — 외래 키 → BIZ_KIOSK_INFO.kiosk_id — NOT NULL
  • machine_name — varchar(20)
  • max_floor — int(11)
  • engine_id — varchar(20)
  • create_id — varchar(20)
  • create_time — datetime
  • update_id — varchar(20)
  • update_time — datetime

BIZ_MACHINE_PRODUCT_PRICE

  • machine_id — varchar(20) — 외래 키 → BIZ_MACHINE_INFO.machine_id — NOT NULL
  • product_id — varchar(20) — 외래 키 → BIZ_PRODUCT_INFO.product_id
  • regular_price — int(11)
  • sales_price — int(11)
  • start_date — date
  • end_date — date
  • create_id — varchar(2)
  • create_time — datetime
  • update_id — varchar(20)
  • update_time — datetime

BIZ_MACHINE_STOCK

  • machine_id — varchar(20) — 외래 키 → BIZ_MACHINE_INFO.machine_id — NOT NULL
  • product_id — varchar(20) — 외래 키 → BIZ_PRODUCT_INFO.product_id
  • count — int(11)
  • create_id — varchar(20)
  • create_time — datetime
  • update_id — varchar(20)
  • update_time — datetime

BIZ_MACHINE_STOCK_HISTORY

  • machine_id — varchar(20) — 외래 키 → BIZ_MACHINE_STOCK.machine_id — NOT NULL
  • product_id — varchar(20) — 외래 키 → BIZ_MACHINE_STOCK.product_id
  • class — varchar(20) — 입고 or 판매 or 불량 or etc.
  • class_id — varchar(20) — 입고ID or 판매ID or etc.
  • count — int(11)
  • apply_yn — bool — 재고 update 성공 or 실패
  • create_id — varchar(20)
  • create_time — datetime
  • update_id — varchar(20)
  • update_time — datetime

BIZ_PAY_INFO

    BIZ_PRODUCT_INFO

    • product_id — varchar(20)
    • product_name — varchar(100)
    • category1 — varchar(50)
    • category2 — varchar(50)
    • category3 — varchar(50)
    • category4 — varchar(50)
    • class — varchar(2) — 완제품, 세트상품
    • barcode — varchar(30)
    • tax_yn — bool
    • url — varchar(200)
    • surtax — bool — 포함 or 별도
    • regular_price — int(11)
    • purchase_price — int(11)
    • weight — int(11)
    • status — varchar(2)
    • create_id — varchar(20)
    • create_time — datetime
    • update_id — varchar(20)
    • update_time — datetime

    BIZ_SALES_DETAIL

    • machine_id — varchar(20) — 외래 키 → BIZ_SALES_HISTORY.machine_id — NOT NULL
    • sales_id — varchar(20) — 외래 키 → BIZ_SALES_HISTORY.sales_id
    • product_id — varchar(20) — 외래 키 → BIZ_PRODUCT_INFO.product_id
    • count — int(11)
    • total_price — int(11)
    • sales_price — int(11)
    • net_price — int(11)
    • surtax_price — int(11)
    • discount_price — int(11)
    • before_count — int(11)
    • after_count — int(11)

    BIZ_SALES_HISTORY

    • machine_id — varchar(20) — 외래 키 → BIZ_MACHINE_INFO.machine_id — NOT NULL
    • sales_id — varchar(20)
    • total_price — int(11)
    • status — varchar(2)
    • sales_price — int(11)
    • net_price — int(11)
    • surtax_price — int(11)
    • discount_total — int(11)
    • sales_time — datetime

    BIZ_STORE_INFO

    • store_id — varchar(20)
    • company_id — varchar(20) — 외래 키 → BIZ_COMPANY_INFO.company_id
    • store_name — varchar(50)
    • address_grp1 — varchar(10)
    • address_grp2 — varchar(10)
    • address_text1 — varchar(200)
    • address_text2 — varchar(200)
    • owner_id — varchar(20)
    • owner_name — varchar(20)
    • tel_no — varchar(20)
    • open_date — date
    • close_date — date
    • use_yn — bool
    • create_id — varchar(20)
    • create_time — datetime
    • update_id — varchar(20)
    • update_time — datetime

    BIZ_STORE_PRODUCT_INFO

    • store_id — varchar(20) — 기본 키 — NOT NULL
    • product_id — varchar(20)
    • product_name — varchar(100)
    • category1 — varchar(50)
    • category2 — varchar(50)
    • category3 — varchar(50)
    • category4 — varchar(50)
    • class — varchar(2) — 완제품, 세트상품
    • barcode — varchar(30)
    • tax_yn — bool
    • url — varchar(200)
    • surtax — bool — 포함 or 별도
    • regular_price — int(11)
    • purchase_price — int(11)
    • weight — int(11)
    • status — varchar(2)
    • create_id — varchar(20)
    • create_time — datetime
    • update_id — varchar(20)
    • update_time — datetime

    BIZ_WH_DETAIL

    • machine_id — varchar(20) — 외래 키 → BIZ_WH_HISTORY.machine_id — NOT NULL
    • wh_id — varchar(20) — 외래 키 → BIZ_WH_HISTORY.wh_id
    • product_id — varchar(20) — 외래 키 → BIZ_PRODUCT_INFO.product_id
    • floor — int(11)
    • wh_count — int(11)
    • before_count — int(11)
    • after_count — int(11)

    BIZ_WH_HISTORY

    • machine_id — varchar(20) — 외래 키 → BIZ_MACHINE_INFO.machine_id — NOT NULL
    • wh_id — varchar(20)
    • status — varchar(2)
    • wh_time — datetime

    고객 (customer)

    • 고객 ID (customer_id) — 기본 키 — NOT NULL
    • 고객 이름 (customer_name)

    구매 (purchase)

    • 구매 ID (purchase_id) — 기본 키 — NOT NULL
    • 매장 ID (store_id) — 외래 키 → 매장.매장 ID — NOT NULL
    • 고객 ID (customer_id) — 외래 키 → 고객.고객 ID — NOT NULL
    • 총매출액 (total_price)
    • 구매일자 (purchase_at)

    구매 상세 (purchase_item)

    • 구매 ID (purchase_id) — 외래 키 → 구매.구매 ID — NOT NULL
    • 상품 ID (product_id) — 외래 키 → 상품.상품 ID — NOT NULL
    • 총매출액 (price)
    • 판매수량 (count)

    매장 (store)

    • 매장 ID (store_id) — 기본 키 — NOT NULL
    • meta

    매장별 가격 (store_product_price)

    • 매장 ID (store_id) — 외래 키 → 매장.매장 ID — NOT NULL
    • 상품 ID (product_id) — 외래 키 → 상품.상품 ID — NOT NULL
    • 매장 판매가 (store_price)
    • meta

    상품 (product)

    • 상품 ID (product_id) — 기본 키 — NOT NULL
    • 상품 이름 (product_name)
    • 가격 (price)

    입고 (import)

    • 입고 ID (import_id) — 기본 키 — NOT NULL
    • 입고일자 (import_at)
    • meta
    • 매장 ID (store_id) — 외래 키 → 매장.매장 ID — NOT NULL

    입고 상세 (import_item)

    • 입고 ID (import_id) — 외래 키 → 입고.입고 ID — NOT NULL
    • 상품 ID (product_id) — 외래 키 → 상품.상품 ID — NOT NULL
    • count