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