erp-lite

Tables (8)

code_master

  • code_id — BIGINT — Primary key — NOT NULL — PK
  • code_group — VARCHAR(50) — NOT NULL — 코드그룹 (TRAN_TYPE, TRAN_STATUS 등)
  • code_value — VARCHAR(50) — NOT NULL — 코드값
  • code_name — VARCHAR(100) — NOT NULL — 코드명
  • sort_order — INT — NOT NULL — 정렬순서
  • is_active — TINYINT(1) — NOT NULL — 사용여부
  • description — VARCHAR(500) — 설명
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME
  • created_by — VARCHAR(50)
  • is_deleted — TINYINT(1) — NOT NULL
  • INDEX — idx_code_group

customer

  • customer_id — BIGINT — Primary key — NOT NULL — PK
  • customer_code — VARCHAR(50) — NOT NULL — 거래처코드
  • customer_name — VARCHAR(200) — NOT NULL — 거래처명
  • customer_type — VARCHAR(20) — NOT NULL — 유형
  • business_no — VARCHAR(20) — 사업자번호
  • ceo_name — VARCHAR(50) — 대표자명
  • phone — VARCHAR(20) — 전화번호
  • email — VARCHAR(100) — 이메일
  • address — VARCHAR(300) — 주소
  • payment_terms — VARCHAR(20) — NOT NULL — 결제조건
  • credit_limit — DECIMAL(15, 2) — 여신한도
  • is_active — TINYINT(1) — NOT NULL — 사용여부
  • description — VARCHAR(500) — 비고
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME
  • created_by — VARCHAR(50)
  • is_deleted — TINYINT(1) — NOT NULL
  • INDEX — idx_customer_name
  • INDEX — idx_customer_type

item

  • item_id — BIGINT — Primary key — NOT NULL — PK
  • item_code — VARCHAR(50) — NOT NULL — 품목코드
  • item_name — VARCHAR(200) — NOT NULL — 품목명
  • category — VARCHAR(100) — 분류
  • unit — VARCHAR(20) — NOT NULL — 단위
  • unit_price — DECIMAL(15, 2) — NOT NULL — 단가
  • cost_price — DECIMAL(15, 2) — 원가
  • safety_stock — INT — 안전재고
  • is_active — TINYINT(1) — NOT NULL — 사용여부
  • description — VARCHAR(500) — 비고
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME
  • created_by — VARCHAR(50)
  • is_deleted — TINYINT(1) — NOT NULL
  • INDEX — idx_item_name
  • INDEX — idx_item_category

stock

  • stock_id — BIGINT — Primary key — NOT NULL — PK
  • item_id — BIGINT — Foreign key → item.item_id — NOT NULL — 품목 FK
  • warehouse_id — BIGINT — Foreign key → warehouse.warehouse_id — NOT NULL — 창고 FK
  • quantity — INT — NOT NULL — 현재고 수량
  • last_in_date — DATE — 최종입고일
  • last_out_date — DATE — 최종출고일
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME
  • INDEX — idx_stock_warehouse

stock_history

  • history_id — BIGINT — Primary key — NOT NULL — PK
  • item_id — BIGINT — Foreign key → item.item_id — NOT NULL — 품목 FK
  • warehouse_id — BIGINT — Foreign key → warehouse.warehouse_id — NOT NULL — 창고 FK
  • transaction_id — BIGINT — Foreign key → transaction.transaction_id — 거래 FK (수기조정 시 NULL)
  • history_date — DATE — NOT NULL — 발생일자
  • history_type — VARCHAR(20) — NOT NULL — 유형 (IN/OUT/ADJUST)
  • quantity — INT — NOT NULL — 변동수량 (항상 양수)
  • before_quantity — INT — NOT NULL — 변동 전 재고
  • after_quantity — INT — NOT NULL — 변동 후 재고
  • reason — VARCHAR(200) — 사유 (조정 시)
  • created_at — DATETIME — NOT NULL
  • created_by — VARCHAR(50)
  • INDEX — idx_history_item
  • INDEX — idx_history_date
  • INDEX — idx_history_transaction

transaction

  • transaction_id — BIGINT — Primary key — NOT NULL — PK
  • transaction_no — VARCHAR(20) — NOT NULL — 거래번호
  • transaction_date — DATE — NOT NULL — 거래일자
  • transaction_type — VARCHAR(20) — NOT NULL — 거래유형 (PURCHASE/SALE/RETURN_IN/RETURN_OUT)
  • transaction_status — VARCHAR(20) — NOT NULL — 상태
  • customer_id — BIGINT — Foreign key → customer.customer_id — NOT NULL — 거래처 FK
  • warehouse_id — BIGINT — Foreign key → warehouse.warehouse_id — NOT NULL — 창고 FK
  • total_quantity — INT — NOT NULL — 총수량
  • total_amount — DECIMAL(15, 2) — NOT NULL — 총금액
  • vat_amount — DECIMAL(15, 2) — NOT NULL — 부가세
  • discount_amount — DECIMAL(15, 2) — NOT NULL — 할인금액
  • net_amount — DECIMAL(15, 2) — NOT NULL — 순금액
  • description — VARCHAR(500) — 비고
  • confirmed_at — DATETIME — 확정일시
  • confirmed_by — VARCHAR(50) — 확정자
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME
  • created_by — VARCHAR(50)
  • is_deleted — TINYINT(1) — NOT NULL
  • INDEX — idx_transaction_date
  • INDEX — idx_transaction_type
  • INDEX — idx_transaction_status
  • INDEX — idx_transaction_customer

transaction_detail

  • detail_id — BIGINT — Primary key — NOT NULL — PK
  • transaction_id — BIGINT — Foreign key → transaction.transaction_id — NOT NULL — 거래 FK
  • line_no — INT — NOT NULL — 행번호
  • item_id — BIGINT — Foreign key → item.item_id — NOT NULL — 품목 FK
  • quantity — INT — NOT NULL — 수량
  • unit_price — DECIMAL(15, 2) — NOT NULL — 단가
  • amount — DECIMAL(15, 2) — NOT NULL — 금액 (수량 x 단가)
  • vat_amount — DECIMAL(15, 2) — NOT NULL — 부가세
  • description — VARCHAR(200) — 비고
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME
  • is_deleted — TINYINT(1) — NOT NULL
  • INDEX — idx_detail_item

warehouse

  • warehouse_id — BIGINT — Primary key — NOT NULL — PK
  • warehouse_code — VARCHAR(20) — NOT NULL — 창고코드
  • warehouse_name — VARCHAR(100) — NOT NULL — 창고명
  • location — VARCHAR(200) — 위치/주소
  • is_active — TINYINT(1) — NOT NULL — 사용여부
  • description — VARCHAR(500) — 비고
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME
  • created_by — VARCHAR(50)
  • is_deleted — TINYINT(1) — NOT NULL