ERPsystem

Tables (45)

app_menus

  • menu_id — BIGINT — Primary key — NOT NULL
  • parent_menu_id — BIGINT — Foreign key → app_menus.menu_id
  • menu_code — VARCHAR(50) — NOT NULL
  • menu_name — VARCHAR(100) — NOT NULL
  • menu_path — VARCHAR(200)
  • sort_order — INT — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

attendance_records

  • attendance_record_id — BIGINT — Primary key — NOT NULL
  • employee_id — BIGINT — Foreign key → employees.employee_id — NOT NULL
  • work_date — DATE — NOT NULL
  • check_in_at — DATETIME
  • check_out_at — DATETIME
  • attendance_status — VARCHAR(30) — NOT NULL
  • memo — VARCHAR(500)

attendance_requests

  • attendance_request_id — BIGINT — Primary key — NOT NULL
  • employee_id — BIGINT — Foreign key → employees.employee_id — NOT NULL
  • request_type — VARCHAR(30) — NOT NULL — 휴가, 반차, 외근 등
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • reason — TEXT
  • approval_status — VARCHAR(30) — NOT NULL
  • requested_at — DATETIME — NOT NULL
  • approved_by — BIGINT — Foreign key → users.user_id
  • approved_at — DATETIME

bom_headers

  • bom_id — BIGINT — Primary key — NOT NULL
  • product_item_id — BIGINT — Foreign key → items.item_id — NOT NULL
  • bom_name — VARCHAR(200) — NOT NULL
  • revision_no — VARCHAR(50) — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

bom_items

  • bom_item_id — BIGINT — Primary key — NOT NULL
  • bom_id — BIGINT — Foreign key → bom_headers.bom_id — NOT NULL
  • component_item_id — BIGINT — Foreign key → items.item_id — NOT NULL
  • required_qty — DECIMAL(18,4) — NOT NULL
  • loss_rate — DECIMAL(8,4) — NOT NULL
  • sort_order — INT — NOT NULL

certificate_issues

  • certificate_id — BIGINT — Primary key — NOT NULL
  • employee_id — BIGINT — Foreign key → employees.employee_id — NOT NULL
  • certificate_type — VARCHAR(50) — NOT NULL
  • issue_no — VARCHAR(100) — NOT NULL
  • issue_date — DATE — NOT NULL
  • purpose — VARCHAR(500)
  • issued_by — BIGINT — Foreign key → users.user_id
  • created_at — DATETIME — NOT NULL

common_code_items

  • code_item_id — BIGINT — Primary key — NOT NULL
  • code_group_id — BIGINT — Foreign key → common_codes.code_group_id — NOT NULL
  • code_value — VARCHAR(50) — NOT NULL
  • code_name — VARCHAR(100) — NOT NULL
  • sort_order — INT — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

common_codes

  • code_group_id — BIGINT — Primary key — NOT NULL
  • group_code — VARCHAR(50) — NOT NULL
  • group_name — VARCHAR(100) — NOT NULL
  • description — VARCHAR(500)
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

delivery_note_lines

  • delivery_line_id — BIGINT — Primary key — NOT NULL
  • delivery_id — BIGINT — Foreign key → delivery_notes.delivery_id — NOT NULL
  • item_id — BIGINT — Foreign key → items.item_id — NOT NULL
  • delivery_qty — DECIMAL(18,4) — NOT NULL
  • unit_price — DECIMAL(18,2) — NOT NULL
  • line_amount — DECIMAL(18,2) — NOT NULL

delivery_notes

  • delivery_id — BIGINT — Primary key — NOT NULL
  • delivery_no — VARCHAR(100) — NOT NULL
  • sales_order_id — BIGINT — Foreign key → sales_orders.sales_order_id
  • customer_id — BIGINT — Foreign key → partners.partner_id — NOT NULL
  • delivery_date — DATE — NOT NULL
  • delivery_status — VARCHAR(30) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

departments

  • department_id — BIGINT — Primary key — NOT NULL
  • parent_department_id — BIGINT — Foreign key → departments.department_id
  • department_code — VARCHAR(50) — NOT NULL
  • department_name — VARCHAR(100) — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

documents

  • document_id — BIGINT — Primary key — NOT NULL
  • project_id — BIGINT — Foreign key → projects.project_id
  • document_type — VARCHAR(50) — NOT NULL
  • document_no — VARCHAR(100)
  • document_name — VARCHAR(200) — NOT NULL
  • file_path — VARCHAR(500)
  • version_no — VARCHAR(50)
  • description — TEXT
  • created_by — BIGINT — Foreign key → users.user_id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

drawing_documents

  • drawing_id — BIGINT — Primary key — NOT NULL
  • project_id — BIGINT — Foreign key → projects.project_id
  • drawing_no — VARCHAR(100) — NOT NULL
  • drawing_name — VARCHAR(200) — NOT NULL
  • revision_no — VARCHAR(50)
  • file_path — VARCHAR(500)
  • approval_status — VARCHAR(30) — NOT NULL
  • created_by — BIGINT — Foreign key → users.user_id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

employees

  • employee_id — BIGINT — Primary key — NOT NULL
  • department_id — BIGINT — Foreign key → departments.department_id
  • employee_no — VARCHAR(50) — NOT NULL
  • employee_name — VARCHAR(100) — NOT NULL
  • position_name — VARCHAR(50)
  • job_title — VARCHAR(50)
  • email — VARCHAR(200)
  • phone — VARCHAR(50)
  • hire_date — DATE
  • resign_date — DATE
  • employment_status — VARCHAR(30) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

evaluation_results

  • evaluation_id — BIGINT — Primary key — NOT NULL
  • employee_id — BIGINT — Foreign key → employees.employee_id — NOT NULL
  • evaluation_year — INT — NOT NULL
  • evaluation_period — VARCHAR(20) — NOT NULL
  • evaluator_id — BIGINT — Foreign key → employees.employee_id
  • total_score — DECIMAL(8,2)
  • grade — VARCHAR(20)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

field_work_reports

  • report_id — BIGINT — Primary key — NOT NULL
  • work_order_id — BIGINT — Foreign key → work_orders.work_order_id
  • project_id — BIGINT — Foreign key → projects.project_id
  • report_date — DATE — NOT NULL
  • worker_employee_id — BIGINT — Foreign key → employees.employee_id
  • work_content — TEXT — NOT NULL
  • completed_qty — DECIMAL(18,4) — NOT NULL
  • defect_qty — DECIMAL(18,4) — NOT NULL
  • issue_content — TEXT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

inventory

  • inventory_id — BIGINT — Primary key — NOT NULL
  • item_id — BIGINT — Foreign key → items.item_id — NOT NULL
  • warehouse_code — VARCHAR(50) — NOT NULL
  • current_qty — DECIMAL(18,4) — NOT NULL
  • safety_qty — DECIMAL(18,4) — NOT NULL
  • updated_at — DATETIME — NOT NULL

inventory_transactions

  • transaction_id — BIGINT — Primary key — NOT NULL
  • item_id — BIGINT — Foreign key → items.item_id — NOT NULL
  • warehouse_code — VARCHAR(50) — NOT NULL
  • transaction_type — VARCHAR(30) — NOT NULL — 입고, 출고, 조정, 생산입고 등
  • reference_type — VARCHAR(50)
  • reference_id — BIGINT
  • qty — DECIMAL(18, 4) — NOT NULL
  • transaction_date — DATETIME — NOT NULL
  • created_by — BIGINT — Foreign key → users.user_id
  • memo — VARCHAR(500)

item_code_requests

  • request_id — BIGINT — Primary key — NOT NULL
  • requested_by — BIGINT — Foreign key → users.user_id
  • request_type — VARCHAR(30) — NOT NULL
  • requested_item_name — VARCHAR(200) — NOT NULL
  • requested_item_type — VARCHAR(30) — NOT NULL
  • specification — VARCHAR(500)
  • reason — TEXT
  • status — VARCHAR(30) — NOT NULL
  • approved_item_id — BIGINT — Foreign key → items.item_id
  • requested_at — DATETIME — NOT NULL
  • approved_at — DATETIME

items

  • item_id — BIGINT — Primary key — NOT NULL
  • item_code — VARCHAR(100) — NOT NULL
  • item_name — VARCHAR(200) — NOT NULL
  • item_type — VARCHAR(30) — NOT NULL — RAW, PRODUCT, SW, ETC 등
  • unit — VARCHAR(20) — NOT NULL
  • specification — VARCHAR(500)
  • standard_price — DECIMAL(18, 2)
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

kpi_entries

  • kpi_id — BIGINT — Primary key — NOT NULL
  • employee_id — BIGINT — Foreign key → employees.employee_id — NOT NULL
  • kpi_year — INT — NOT NULL
  • kpi_period — VARCHAR(20) — NOT NULL — 상반기, 하반기, 분기 등
  • kpi_title — VARCHAR(200) — NOT NULL
  • target_value — DECIMAL(18, 4)
  • actual_value — DECIMAL(18, 4)
  • achievement_rate — DECIMAL(8, 2)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

outsourcing_orders

  • outsourcing_id — BIGINT — Primary key — NOT NULL
  • project_id — BIGINT — Foreign key → projects.project_id
  • vendor_id — BIGINT — Foreign key → partners.partner_id — NOT NULL
  • outsourcing_name — VARCHAR(200) — NOT NULL
  • request_date — DATE
  • due_date — DATE
  • amount — DECIMAL(18,2)
  • status — VARCHAR(30) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

partners

  • partner_id — BIGINT — Primary key — NOT NULL
  • partner_type — VARCHAR(30) — NOT NULL — 협력사, 거래선, 고객사 등
  • partner_code — VARCHAR(50) — NOT NULL
  • partner_name — VARCHAR(200) — NOT NULL
  • business_no — VARCHAR(50)
  • ceo_name — VARCHAR(100)
  • manager_name — VARCHAR(100)
  • phone — VARCHAR(50)
  • email — VARCHAR(200)
  • address — VARCHAR(500)
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

production_schedules

  • schedule_id — BIGINT — Primary key — NOT NULL
  • project_id — BIGINT — Foreign key → projects.project_id
  • product_item_id — BIGINT — Foreign key → items.item_id — NOT NULL
  • plan_date — DATE — NOT NULL
  • planned_qty — DECIMAL(18,4) — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • manager_employee_id — BIGINT — Foreign key → employees.employee_id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

projects

  • project_id — BIGINT — Primary key — NOT NULL
  • customer_id — BIGINT — Foreign key → partners.partner_id
  • project_code — VARCHAR(50) — NOT NULL
  • project_name — VARCHAR(200) — NOT NULL
  • project_type — VARCHAR(50)
  • start_date — DATE
  • end_date — DATE
  • status — VARCHAR(30) — NOT NULL
  • manager_employee_id — BIGINT — Foreign key → employees.employee_id
  • description — TEXT
  • created_by — BIGINT — Foreign key → users.user_id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

purchase_order_lines

  • purchase_order_line_id — BIGINT — Primary key — NOT NULL
  • purchase_order_id — BIGINT — Foreign key → purchase_orders.purchase_order_id — NOT NULL
  • item_id — BIGINT — Foreign key → items.item_id — NOT NULL
  • order_qty — DECIMAL(18,4) — NOT NULL
  • unit_price — DECIMAL(18,2) — NOT NULL
  • line_amount — DECIMAL(18,2) — NOT NULL

purchase_orders

  • purchase_order_id — BIGINT — Primary key — NOT NULL
  • purchase_order_no — VARCHAR(100) — NOT NULL
  • vendor_id — BIGINT — Foreign key → partners.partner_id — NOT NULL
  • purchase_request_id — BIGINT — Foreign key → purchase_requests.purchase_request_id
  • order_date — DATE — NOT NULL
  • due_date — DATE
  • order_status — VARCHAR(30) — NOT NULL
  • total_amount — DECIMAL(18,2) — NOT NULL
  • created_by — BIGINT — Foreign key → users.user_id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

purchase_request_lines

  • purchase_request_line_id — BIGINT — Primary key — NOT NULL
  • purchase_request_id — BIGINT — Foreign key → purchase_requests.purchase_request_id — NOT NULL
  • item_id — BIGINT — Foreign key → items.item_id — NOT NULL
  • request_qty — DECIMAL(18,4) — NOT NULL
  • desired_date — DATE
  • memo — VARCHAR(500)

purchase_requests

  • purchase_request_id — BIGINT — Primary key — NOT NULL
  • request_no — VARCHAR(100) — NOT NULL
  • project_id — BIGINT — Foreign key → projects.project_id
  • requested_by — BIGINT — Foreign key → users.user_id
  • request_date — DATE — NOT NULL
  • request_status — VARCHAR(30) — NOT NULL
  • memo — TEXT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

purchase_settlements

  • purchase_settlement_id — BIGINT — Primary key — NOT NULL
  • vendor_id — BIGINT — Foreign key → partners.partner_id — NOT NULL
  • purchase_order_id — BIGINT — Foreign key → purchase_orders.purchase_order_id
  • settlement_no — VARCHAR(100) — NOT NULL
  • settlement_date — DATE — NOT NULL
  • settlement_amount — DECIMAL(18,2) — NOT NULL
  • settlement_status — VARCHAR(30) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

quote_lines

  • quote_line_id — BIGINT — Primary key — NOT NULL
  • quote_id — BIGINT — Foreign key → quotes.quote_id — NOT NULL
  • sales_code_id — BIGINT — Foreign key → sales_codes.sales_code_id
  • item_id — BIGINT — Foreign key → items.item_id
  • item_name — VARCHAR(200) — NOT NULL
  • quote_qty — DECIMAL(18,4) — NOT NULL
  • unit_price — DECIMAL(18,2) — NOT NULL
  • line_amount — DECIMAL(18,2) — NOT NULL

quotes

  • quote_id — BIGINT — Primary key — NOT NULL
  • quote_no — VARCHAR(100) — NOT NULL
  • project_id — BIGINT — Foreign key → projects.project_id
  • customer_id — BIGINT — Foreign key → partners.partner_id — NOT NULL
  • quote_date — DATE — NOT NULL
  • valid_until — DATE
  • quote_status — VARCHAR(30) — NOT NULL
  • total_amount — DECIMAL(18,2) — NOT NULL
  • created_by — BIGINT — Foreign key → users.user_id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

receipt_lines

  • receipt_line_id — BIGINT — Primary key — NOT NULL
  • receipt_id — BIGINT — Foreign key → receipts.receipt_id — NOT NULL
  • item_id — BIGINT — Foreign key → items.item_id — NOT NULL
  • receipt_qty — DECIMAL(18,4) — NOT NULL
  • accepted_qty — DECIMAL(18,4) — NOT NULL
  • rejected_qty — DECIMAL(18,4) — NOT NULL

receipts

  • receipt_id — BIGINT — Primary key — NOT NULL
  • receipt_no — VARCHAR(100) — NOT NULL
  • purchase_order_id — BIGINT — Foreign key → purchase_orders.purchase_order_id
  • receipt_date — DATE — NOT NULL
  • receipt_status — VARCHAR(30) — NOT NULL
  • inspector_employee_id — BIGINT — Foreign key → employees.employee_id
  • memo — TEXT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

role_menu_permissions

  • permission_id — BIGINT — Primary key — NOT NULL
  • role_id — BIGINT — Foreign key → roles.role_id — NOT NULL
  • menu_id — BIGINT — Foreign key → app_menus.menu_id — NOT NULL
  • can_read — TINYINT(1) — NOT NULL
  • can_create — TINYINT(1) — NOT NULL
  • can_update — TINYINT(1) — NOT NULL
  • can_delete — TINYINT(1) — NOT NULL
  • can_export — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL

roles

  • role_id — BIGINT — Primary key — NOT NULL
  • role_code — VARCHAR(50) — NOT NULL
  • role_name — VARCHAR(100) — NOT NULL
  • description — VARCHAR(500)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

sales_codes

  • sales_code_id — BIGINT — Primary key — NOT NULL
  • sales_code — VARCHAR(100) — NOT NULL
  • item_id — BIGINT — Foreign key → items.item_id
  • sales_name — VARCHAR(200) — NOT NULL
  • unit_price — DECIMAL(18,2) — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

sales_order_lines

  • sales_order_line_id — BIGINT — Primary key — NOT NULL
  • sales_order_id — BIGINT — Foreign key → sales_orders.sales_order_id — NOT NULL
  • item_id — BIGINT — Foreign key → items.item_id — NOT NULL
  • order_qty — DECIMAL(18,4) — NOT NULL
  • unit_price — DECIMAL(18,2) — NOT NULL
  • line_amount — DECIMAL(18,2) — NOT NULL

sales_orders

  • sales_order_id — BIGINT — Primary key — NOT NULL
  • sales_order_no — VARCHAR(100) — NOT NULL
  • quote_id — BIGINT — Foreign key → quotes.quote_id
  • project_id — BIGINT — Foreign key → projects.project_id
  • customer_id — BIGINT — Foreign key → partners.partner_id — NOT NULL
  • order_date — DATE — NOT NULL
  • due_date — DATE
  • order_status — VARCHAR(30) — NOT NULL
  • total_amount — DECIMAL(18,2) — NOT NULL
  • created_by — BIGINT — Foreign key → users.user_id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

sales_settlements

  • sales_settlement_id — BIGINT — Primary key — NOT NULL
  • customer_id — BIGINT — Foreign key → partners.partner_id — NOT NULL
  • sales_order_id — BIGINT — Foreign key → sales_orders.sales_order_id
  • settlement_no — VARCHAR(100) — NOT NULL
  • settlement_date — DATE — NOT NULL
  • settlement_amount — DECIMAL(18,2) — NOT NULL
  • settlement_status — VARCHAR(30) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

software_assets

  • software_id — BIGINT — Primary key — NOT NULL
  • project_id — BIGINT — Foreign key → projects.project_id
  • software_name — VARCHAR(200) — NOT NULL
  • version_no — VARCHAR(50)
  • license_key — VARCHAR(200)
  • manager_employee_id — BIGINT — Foreign key → employees.employee_id
  • status — VARCHAR(30) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

source_repositories

  • repository_id — BIGINT — Primary key — NOT NULL
  • project_id — BIGINT — Foreign key → projects.project_id
  • repository_name — VARCHAR(200) — NOT NULL
  • repository_url — VARCHAR(500)
  • branch_name — VARCHAR(100)
  • description — TEXT
  • created_by — BIGINT — Foreign key → users.user_id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

user_roles

  • user_role_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • role_id — BIGINT — Foreign key → roles.role_id — NOT NULL
  • created_at — DATETIME — NOT NULL

users

  • user_id — BIGINT — Primary key — NOT NULL
  • employee_id — BIGINT — Foreign key → employees.employee_id
  • login_id — VARCHAR(100) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • user_name — VARCHAR(100) — NOT NULL
  • email — VARCHAR(200)
  • is_active — TINYINT(1) — NOT NULL
  • last_login_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

work_orders

  • work_order_id — BIGINT — Primary key — NOT NULL
  • schedule_id — BIGINT — Foreign key → production_schedules.schedule_id
  • work_order_no — VARCHAR(100) — NOT NULL
  • product_item_id — BIGINT — Foreign key → items.item_id — NOT NULL
  • order_qty — DECIMAL(18,4) — NOT NULL
  • completed_qty — DECIMAL(18,4) — NOT NULL
  • defect_qty — DECIMAL(18,4) — NOT NULL
  • work_status — VARCHAR(30) — NOT NULL
  • start_at — DATETIME
  • end_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL