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 — 주문 거래처