반정규화적용 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