ERP
Tables (32)
OrderItem (주문상세)
- order_item_id — INT — Primary key — NOT NULL — 주문 상세 ID
- quantity — INT — NOT NULL — 수량
- unit_price — DECIMAL(12,2) — NOT NULL — 단가
- discount — DECIMAL(5,2) — 할인율
- subtotal — DECIMAL(12,2) — 소계
- tax — DECIMAL(12,2) — 부가세
- order_id — INT — Foreign key → 주문.order_id — NOT NULL — 주문 ID
- product_id — BIGINT — Foreign key → product.product_id — NOT NULL — 상품 ID
attendance
- attendance_id — BIGINT — Primary key
- employee_id — BIGINT — Foreign key → employee.employee_id — NOT NULL
- work_date — DATE — NOT NULL
- check_in_time — TIME
- check_out_time — TIME
- status (ENUM('출근','지각','결근','휴가')) — VARCHAR(20) — NOT NULL
- overtime_minutes — INT
- note — VARCHAR(255)
board_post
- post_id — BIGINT — Primary key — NOT NULL
- type — ENUM('NOTICE','INQUIRY') — NOT NULL
- title — VARCHAR(200) — NOT NULL
- content — TEXT — NOT NULL
- writer_user_id — BIGINT — Foreign key → user.user_id
- status — VARCHAR(20) — NOT NULL
- pinned_yn — CHAR(1)
- created_at — DATETIME
- updated_at — DATETIME
- handled_by_id — BIGINT — Foreign key → user.user_id
- answered_at — DATETIME
- answer_content — TEXT
category
- category_id — BiGINT — Primary key — NOT NULL
- name — VARCHAR(100) — NOT NULL
- level — TINYINT — NOT NULL
- parent_id (id2) — BiGINT — Foreign key → category.category_id
- is_active — TINYINT(1)
client
- client_id — BIGINT — Primary key — NOT NULL
- name — VARCHAR(200) — NOT NULL
- type — ENUM('CUSTOMER', 'SUPPLIER', 'BOTH') — NOT NULL
- credit_limit — DECIMAL(15,2)
- payment_terms — VARCHAR(100)
- biz_no — VARCHAR(50)
- billing_addr — VARCHAR(255)
- shipping_addr — VARCHAR(255)
- is_active — TINYINT(1)
client_contact
- client_contact_id — BIGINT — Primary key — NOT NULL
- client_id — BIGINT — Foreign key → client.client_id — NOT NULL
- name — VARCHAR(100) — NOT NULL
- phone — VARCHAR(50)
- position — VARCHAR(100)
- email — VARCHAR(100)
- is_primary — TINYINT(1)
department
- dept_id — BIGINT — Primary key — NOT NULL — 부서 ID
- name — VARCHAR(100) — NOT NULL — 부서명
- parent_id — BIGINT — Foreign key → department.dept_id — 상위 부서 ID
employee
- employee_id — BIGINT — Primary key
- name — VARCHAR(50) — NOT NULL
- dept_id — BIGINT — Foreign key → department.dept_id — NOT NULL
- rank_id — BIGINT — Foreign key → position.position_id — NOT NULL — type:rank를 참조
- title_id — BIGINT — Foreign key → position.position_id — type:title을 참조
- hire_date — DATE — NOT NULL
- retire_date — DATE
- status — ENUM('재직','휴직','퇴사') — NOT NULL
- phone — VARCHAR(30)
- email — VARCHAR(100)
- address — VARCHAR(255)
- birth_date — DATE
- base_salary — DECIMAL(15,2) — NOT NULL
- bank_name — VARCHAR(50)
- bank_account — VARCHAR(50)
- created_at — DATETIME
- updated_at — DATETIME
employee_history
- history_id — BIGINT — Primary key — NOT NULL — 변경내역 ID
- change_type — VARCHAR(30) — NOT NULL — 변경 타입
- change_date — DATE — NOT NULL — 변경 날짜
- before_dept_id — BIGINT — Foreign key → department.dept_id — 변경 전 부서
- after_dept_id — BIGINT — Foreign key → department.dept_id — 변경 후 부서
- before_position_id — BIGINT — Foreign key → position.position_id — 변경 전 직책/직급
- after_position_id — BIGINT — Foreign key → position.position_id — 변경 후 직책/직급
- note — VARCHAR(255) — 세부사항
inventory
- inventory_id — BIGINT — Primary key — NOT NULL
- product_id — BIGINT — Foreign key → product.product_id — NOT NULL
- warehouse_id — BIGINT — Foreign key → warehouse.warehouse_id — NOT NULL
- quantity — INT — NOT NULL
- safety_stock_qty — INT — NOT NULL
inventory_transaction
- inventory_transaction_id — BIGINT — Primary key — NOT NULL
- product_id — BIGINT — Foreign key → product.product_id — NOT NULL
- warehouse_id — BIGINT — Foreign key → warehouse.warehouse_id — NOT NULL
- client_id — BIGINT — Foreign key → client.client_id — NOT NULL
- type — ENUM('IN', 'OUT', 'ADJUST') — NOT NULL
- quantity — INT — NOT NULL
- reason — VARCHAR(200)
- related_client_id — BIGINT
- occurred_at — CURRENT_TIMESTAMP — NOT NULL
menu
- menu_id — BIGINT — Primary key
- name — VARCHAR(100) — NOT NULL
- path — VARCHAR(200) — NOT NULL
- parent_id — BIGINT — Foreign key → menu.menu_id
- sort_order — INT
- use — CHAR(1)
ops_api_status
- api_id — BIGINT — Primary key
- api_name — VARCHAR(100) — NOT NULL
- health_url — VARCHAR(255) — NOT NULL
- last_status — ENUM('UP','DOWN','DEGRADED') — NOT NULL
- last_response_ms — INT
- last_checked_at — DATETIME
ops_backup
- backup_id — BIGINT — Primary key
- file_name — VARCHAR(255) — NOT NULL
- file_path — VARCHAR(500) — NOT NULL
- file_size — BIGINT
- backup_time — DATETIME — NOT NULL
- created_by — VARCHAR(50)
ops_log
- log_id — BIGINT — Primary key
- level — VARCHAR(10)
- category — ENUM('SYSTEM','AUDIT') — NOT NULL
- log_time — DATETIME — NOT NULL
- user_id — BIGINT — Foreign key → user.user_id
- action — VARCHAR(50)
- target — VARCHAR(100)
- target_id — BIGINT
- path — VARCHAR(255)
- ip_address — VARCHAR(45)
- message — VARCHAR(500) — NOT NULL
- stack_trace — TEXT
- logger_name — VARCHAR(255)
- thread_name — VARCHAR(100)
payroll
- payroll_id — BIGINT — Primary key
- employee_id — BIGINT — Foreign key → employee.employee_id — NOT NULL
- year — INT — NOT NULL
- month — INT — NOT NULL
- pay_date — DATE
- status — ENUM('미계산','계산완료','확정') — NOT NULL
payroll_detail
- payroll_detail_id — BIGINT — Primary key — NOT NULL
- payroll_id — BIGINT — Foreign key → payroll.payroll_id — NOT NULL
- payroll_item_id — BIGINT — Foreign key → payroll_item.payroll_item_id — NOT NULL
- amount — DECIMAL(15,2) — NOT NULL
- rate — DECIMAL(10,4)
payroll_item
- payroll_item_id — BIGINT — Primary key — NOT NULL
- item_name — VARCHAR(100) — NOT NULL
- item_type — ENUM('ALLOWANCE','DEDUCTION') — NOT NULL
- calc_type — ENUM('FIXED','RATE') — NOT NULL
- amount — DECIMAL(15,2)
- rate — DECIMAL(10,4)
- use — CHAR(1) — Y = 사용, N = 미사용
position
- position_id — BIGINT — Primary key — NOT NULL — 직책/직급 ID
- type — ENUM('RANK', 'TITLE') — NOT NULL — 직책/직급 선택
- name — VARCHAR(50) — NOT NULL — 직책/직급 명
- sort_order — INT — NOT NULL — 정렬 시 기준
- use_yn — CHAR(1) — NOT NULL — Y = 사용, N = 미사용
product
- product_id — BIGINT — Primary key — NOT NULL
- category_id — BiGINT — Foreign key → category.category_id — NOT NULL
- name — VARCHAR(200) — NOT NULL
- sku — VARCHAR(100) — NOT NULL
- barcode — VARCHAR(100)
- default_supplier_id — BIGINT
- cost_price — DECIMAL(15,2)
- unit_price — DECIMAL(15,2)
- status — ENUM('ACTIVE', 'DISCONTINUED') — NOT NULL
product_supplier
- product_supplier_id — BIGINT — Primary key — NOT NULL
- client_id — BIGINT — Foreign key → client.client_id — NOT NULL
- supplier_sku — VARCHAR(100)
- lead_time — INT
- is_primary — TINYINT(1)
role
- role_id — BIGINT — Primary key
- role_name — VARCHAR(100) — NOT NULL
- description — VARCHAR(255)
- status — ENUM('활성','비활성') — NOT NULL
role_menu
- role_id — BIGINT — Foreign key → role.role_id — NOT NULL
- menu_id — BIGINT — Foreign key → menu.menu_id — NOT NULL
- can_read — TINYINT(1)
- can_write — TINYINT(1)
- can_delete — TINYINT(1)
user
- user_id — BIGINT — Primary key
- username — VARCHAR(50) — NOT NULL
- password — VARCHAR(255) — NOT NULL
- name — VARCHAR(50) — NOT NULL
- email — VARCHAR(100)
- status — ENUM('활성','비활성') — NOT NULL
- last_login_at — DATETIME
user_role
- user_id — BIGINT — Foreign key → user.user_id — NOT NULL
- role_id — BIGINT — Foreign key → role.role_id — NOT NULL
warehouse
- warehouse_id — BIGINT — Primary key — NOT NULL
- name — VARCHAR(150) — NOT NULL
- code — VARCHAR(50) — NOT NULL
- location — VARCHAR(255)
- manager — VARCHAR(100)
- capacity — INT
- is_active — TINYINT(1)
결제 (Payment)
- payment_id — INT — Primary key — NOT NULL — 결제 ID
- payment_date — DATETIME — 결제일
- amount — DECIMAL(12,2) — NOT NULL — 결제 금액
- method — VARCHAR(20) — 결제수단 (카드/현금/계좌이체)
- status — VARCHAR(20) — 결제 상태 (완료/대기/취소)
- order_id — INT — Foreign key → 주문.order_id — NOT NULL — 주문 고유 ID
계정과목 (Account)
- account_id — INT — Primary key — NOT NULL — 계정과목 ID
- name — VARCHAR(50) — NOT NULL — 계정과목명
- type — VARCHAR(20) — NOT NULL — 자산/부채/자본/수익/비용
- parent_account_id — INT — Foreign key → 계정과목.account_id — NOT NULL — 상위 계정
세금계산서 (Invoice)
- invoice_id — INT — Primary key — NOT NULL — 세금계산서 ID
- invoice_date — DATETIME — 발행일
- total_amount — DECIMAL(12,2) — 총액
- tax_amount — DECIMAL(12,2) — 세액
- type — VARCHAR(10) — NOT NULL — 매출/매입 구분
- status — VARCHAR(20) — 발행 상태 (미발행/발행/수정)
- order_id — INT — Foreign key → 주문.order_id — NOT NULL — 연결 주문
전표 (JournalEntry)
- journal_id — INT — Primary key — NOT NULL — 전표 ID
- entry_date — DATETIME — 전표일
- description — VARCHAR(200) — 설명
- status — VARCHAR(20) — 전표 상태 (임시/확정/마감)
- employee_id — BIGINT — Foreign key → employee.employee_id — 생성자
전표항목 (JournalItem)
- journal_item_id — INT — Primary key — NOT NULL — 전표 항목 ID
- debit — DECIMAL(12,2) — 차변 금액
- credit — DECIMAL(12,2) — 대변 금액
- description — VARCHAR(200) — 설명
- account_id — INT — Foreign key → 계정과목.account_id — NOT NULL — 계정과목 ID
- journal_id — INT — Foreign key → 전표.journal_id — NOT NULL — 전표 ID
주문 (Order)
- order_id — INT — Primary key — NOT NULL — 주문 고유 ID
- order_date — DATETIME — 주문일
- status — VARCHAR(20) — 주문 상태 (등록/출고/완료/취소)
- total_amount — DECIMAL(12,2) — 주문 총액
- total_tax — DECIMAL(12,2) — 부가세 총액
- total_profit — DECIMAL(12,2) — 주문별 예상 이익
- customer_id (client_id) — BIGINT — Foreign key → client.client_id — 주문 거래처