반정규화적용 erd

테이블 (7)

bid_submission

  • id — BIGINT — 기본 키 — NOT NULL
  • proposal_id — BIGINT — NOT NULL
  • supplier_id — BIGINT — 외래 키 → supplier.id — NOT NULL
  • supplier_name — VARCHAR(200) — NOT NULL — 협력사명 (반정규화)
  • amount — BIGINT
  • delivery_days — INT
  • tech_score — DECIMAL(5, 2)
  • price_score — DECIMAL(5, 2)
  • esg_score — DECIMAL(5, 2)
  • total_score — DECIMAL(5, 2)
  • status — VARCHAR(30) — NOT NULL
  • note — TEXT
  • submitted_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_bid_supplier_id
  • INDEX — idx_bid_supplier_name

contract

  • id — BIGINT — 기본 키 — NOT NULL
  • con_number — VARCHAR(50) — NOT NULL
  • supplier_id — BIGINT — 외래 키 → supplier.id — NOT NULL
  • supplier_name — VARCHAR(200) — NOT NULL — 협력사명 (반정규화)
  • title — VARCHAR(300)
  • start_date — DATE
  • end_date — DATE
  • amount — BIGINT
  • status — VARCHAR(30) — NOT NULL
  • note — TEXT
  • buyer_id — BIGINT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • INDEX — idx_con_supplier_id
  • INDEX — idx_con_supplier_name

delivery

  • id — BIGINT — 기본 키 — NOT NULL
  • po_id — BIGINT — 외래 키 → purchase_order.id — NOT NULL — 발주 ID (FK)
  • warehouse_id — BIGINT
  • supplier_id — BIGINT — 외래 키 → supplier.id — NOT NULL
  • supplier_name — VARCHAR(200) — NOT NULL — 협력사명 (반정규화)
  • scheduled_date — DATE
  • actual_date — DATE
  • status — VARCHAR(30) — NOT NULL
  • delay_flag — TINYINT(1) — NOT NULL
  • delay_days — INT — NOT NULL
  • total_amount — BIGINT
  • delivery_date — DATE
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • INDEX — idx_del_supplier_id
  • INDEX — idx_del_supplier_name
  • INDEX — idx_del_po_id

purchase_order

  • id — BIGINT — 기본 키 — NOT NULL
  • po_number — VARCHAR(50) — NOT NULL — 발주 번호
  • supplier_id — BIGINT — 외래 키 → supplier.id — NOT NULL — 협력사 ID (FK)
  • supplier_name — VARCHAR(200) — NOT NULL — 협력사명 (반정규화)
  • supplier_code — VARCHAR(50) — NOT NULL — 협력사 코드 (반정규화)
  • status — VARCHAR(30) — NOT NULL
  • total_amount — BIGINT — NOT NULL
  • requested_date — DATE
  • approved_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • buyer_id — BIGINT — 구매 담당자
  • approver_id — BIGINT
  • INDEX — idx_po_supplier_id
  • INDEX — idx_po_supplier_name
  • INDEX — idx_po_status
  • INDEX — idx_po_created

purchase_request

  • id — BIGINT — 기본 키 — NOT NULL
  • req_number — VARCHAR(50) — NOT NULL
  • supplier_id — BIGINT — 협력사 ID (선택)
  • supplier_name — VARCHAR(200) — NOT NULL — 협력사명 (반정규화)
  • title — VARCHAR(300) — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • total_amount — BIGINT — NOT NULL
  • urgency — VARCHAR(20)
  • requester_id — BIGINT
  • department_id — BIGINT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • INDEX — idx_pr_supplier_id
  • INDEX — idx_pr_supplier_name
  • INDEX — idx_pr_status

supplier

  • id — BIGINT — 기본 키 — NOT NULL
  • code — VARCHAR(50) — NOT NULL — 협력사 코드
  • name — VARCHAR(200) — NOT NULL — 협력사명
  • biz_no — VARCHAR(20) — 사업자등록번호
  • email — VARCHAR(200)
  • contact_phone — VARCHAR(30)
  • industry — VARCHAR(100)
  • address — VARCHAR(300)
  • status — VARCHAR(20) — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • INDEX — idx_supplier_code
  • INDEX — idx_supplier_name

tax_invoice

  • id — BIGINT — 기본 키 — NOT NULL
  • invoice_number — VARCHAR(50) — NOT NULL
  • po_id — BIGINT
  • supplier_id — BIGINT — 외래 키 → supplier.id — NOT NULL
  • supplier_name — VARCHAR(200) — NOT NULL — 협력사명 (반정규화)
  • supply_amount — BIGINT — NOT NULL
  • tax_amount — BIGINT — NOT NULL
  • total_amount — BIGINT — NOT NULL
  • issue_date — DATE
  • due_date — DATE
  • confirm_date — DATE
  • status — VARCHAR(30) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_inv_supplier_id
  • INDEX — idx_inv_supplier_name