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