hyungi
5539b09fd8
- 새로운 DB 스키마(v2) 추가 (테이블명 snake_case, FK 적용) - 룰.md에 API 성능 관리 규칙 추가 - 로그인 관련 로직을 새로운 스키마에 맞게 수정 - Service와 Model의 역할 분리를 명확하게 리팩토링
297 lines
14 KiB
SQL
297 lines
14 KiB
SQL
-- Hyungi Technical Korea - Database Schema v2
|
|
-- 개선 사항:
|
|
-- 1. 모든 테이블 및 컬럼 이름을 snake_case로 통일
|
|
-- 2. 데이터 무결성 강화를 위한 외래 키(Foreign Key) 제약조건 추가
|
|
-- 3. 유사/중복 테이블 통합 및 정리 제안 (예: tasks, work_types)
|
|
-- 4. 컬럼의 기본값(Default), 코멘트 등 명확화
|
|
|
|
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
|
|
START TRANSACTION;
|
|
SET time_zone = "+09:00";
|
|
|
|
-- =================================================================
|
|
-- 1. 인증 및 사용자 관련 (Auth Domain)
|
|
-- =================================================================
|
|
|
|
-- 사용자 계정 정보
|
|
CREATE TABLE `users` (
|
|
`user_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`username` varchar(100) NOT NULL,
|
|
`password` varchar(255) NOT NULL,
|
|
`name` varchar(50) DEFAULT NULL,
|
|
`email` varchar(255) DEFAULT NULL,
|
|
`role` varchar(30) DEFAULT 'user' COMMENT '역할 (system, admin, leader, user)',
|
|
`access_level` varchar(30) DEFAULT NULL COMMENT '접근 레벨 (레거시 필드, role로 통합 고려)',
|
|
`worker_id` int(11) DEFAULT NULL COMMENT '연결된 작업자 ID',
|
|
`is_active` tinyint(1) DEFAULT 1 COMMENT '계정 활성화 여부',
|
|
`last_login_at` datetime DEFAULT NULL COMMENT '마지막 로그인 시간',
|
|
`password_changed_at` datetime DEFAULT NULL,
|
|
`failed_login_attempts` int(11) DEFAULT 0,
|
|
`locked_until` datetime DEFAULT NULL,
|
|
`created_at` timestamp NULL DEFAULT current_timestamp(),
|
|
`updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
PRIMARY KEY (`user_id`),
|
|
UNIQUE KEY `username` (`username`),
|
|
KEY `fk_users_worker_id` (`worker_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 로그인 이력
|
|
CREATE TABLE `login_logs` (
|
|
`log_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`user_id` int(11) DEFAULT NULL,
|
|
`login_time` datetime DEFAULT current_timestamp(),
|
|
`ip_address` varchar(45) DEFAULT NULL,
|
|
`user_agent` text DEFAULT NULL,
|
|
`login_status` enum('success','failed','locked') DEFAULT 'success',
|
|
`failure_reason` varchar(100) DEFAULT NULL,
|
|
PRIMARY KEY (`log_id`),
|
|
KEY `fk_login_logs_user_id` (`user_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 비밀번호 변경 이력
|
|
CREATE TABLE `password_change_logs` (
|
|
`log_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`user_id` int(11) NOT NULL,
|
|
`changed_by_user_id` int(11) DEFAULT NULL COMMENT '누가 변경했는지 (관리자)',
|
|
`changed_at` datetime DEFAULT current_timestamp(),
|
|
`change_type` enum('self','admin','reset','initial') DEFAULT 'self',
|
|
`ip_address` varchar(45) DEFAULT NULL,
|
|
PRIMARY KEY (`log_id`),
|
|
KEY `fk_pw_change_user_id` (`user_id`),
|
|
KEY `fk_pw_change_changed_by` (`changed_by_user_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
|
|
-- =================================================================
|
|
-- 2. 기초 정보 (Master Data)
|
|
-- =================================================================
|
|
|
|
-- 프로젝트 정보
|
|
CREATE TABLE `projects` (
|
|
`project_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`job_no` varchar(50) NOT NULL,
|
|
`project_name` varchar(255) NOT NULL,
|
|
`contract_date` date DEFAULT NULL,
|
|
`due_date` date DEFAULT NULL,
|
|
`delivery_method` varchar(100) DEFAULT NULL,
|
|
`site` varchar(100) DEFAULT NULL,
|
|
`pm` varchar(100) DEFAULT NULL,
|
|
`created_at` timestamp NULL DEFAULT current_timestamp(),
|
|
`updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
PRIMARY KEY (`project_id`),
|
|
UNIQUE KEY `job_no` (`job_no`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 작업자(인력) 정보
|
|
CREATE TABLE `workers` (
|
|
`worker_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`worker_name` varchar(100) NOT NULL,
|
|
`job_type` varchar(100) DEFAULT NULL COMMENT '직종',
|
|
`join_date` date DEFAULT NULL COMMENT '입사일',
|
|
`status` varchar(20) DEFAULT 'active' COMMENT '상태 (active, inactive)',
|
|
`created_at` timestamp NULL DEFAULT current_timestamp(),
|
|
`updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
PRIMARY KEY (`worker_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 작업자 그룹 (팀)
|
|
CREATE TABLE `worker_groups` (
|
|
`group_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`group_name` varchar(100) DEFAULT NULL,
|
|
`group_leader_id` int(11) NOT NULL COMMENT '그룹장 user_id',
|
|
`worker_id` int(11) NOT NULL COMMENT '소속 작업자 worker_id',
|
|
`is_active` tinyint(1) DEFAULT 1,
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
PRIMARY KEY (`group_id`),
|
|
KEY `fk_w_groups_leader_id` (`group_leader_id`),
|
|
KEY `fk_w_groups_worker_id` (`worker_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 표준 작업(공수) 종류 (기존 Tasks 와 work_types 통합 제안)
|
|
CREATE TABLE `tasks` (
|
|
`task_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`task_category` varchar(255) NOT NULL COMMENT '작업 대분류 (예: PKG, Vessel)',
|
|
`task_subcategory` varchar(255) DEFAULT NULL COMMENT '작업 중분류 (예: Pipe Pre-Fabrication)',
|
|
`task_name` varchar(255) NOT NULL COMMENT '실제 작업명 (예: 취부&용접)',
|
|
`description` text DEFAULT NULL,
|
|
`is_active` tinyint(1) DEFAULT 1,
|
|
`created_at` timestamp NULL DEFAULT current_timestamp(),
|
|
`updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
PRIMARY KEY (`task_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 파이프 사양(Spec) 정보
|
|
CREATE TABLE `pipe_specs` (
|
|
`spec_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`material` varchar(50) NOT NULL COMMENT '재질 (예: SS400, STS304)',
|
|
`diameter_in` varchar(10) NOT NULL COMMENT '직경 (inch, 예: 2")',
|
|
`schedule` varchar(50) NOT NULL COMMENT '스케줄 (예: STD, SCH10, SCH40)',
|
|
PRIMARY KEY (`spec_id`),
|
|
UNIQUE KEY `uk_pipe_specs` (`material`,`diameter_in`,`schedule`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
-- 공장 설비/장비 목록
|
|
CREATE TABLE `equipment_list` (
|
|
`equipment_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`factory_id` int(11) DEFAULT NULL COMMENT '소속 공장 ID',
|
|
`equipment_name` varchar(255) NOT NULL,
|
|
`model` varchar(100) DEFAULT NULL,
|
|
`status` varchar(50) DEFAULT 'operational',
|
|
`purchase_date` date DEFAULT NULL,
|
|
`description` text DEFAULT NULL,
|
|
`created_at` timestamp NULL DEFAULT current_timestamp(),
|
|
`updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
PRIMARY KEY (`equipment_id`),
|
|
KEY `fk_equip_factory_id` (`factory_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 공장 구역 정보
|
|
CREATE TABLE `factory_info` (
|
|
`factory_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`factory_name` varchar(255) NOT NULL,
|
|
`address` varchar(255) DEFAULT NULL,
|
|
`description` text DEFAULT NULL,
|
|
`map_image_url` varchar(255) DEFAULT NULL,
|
|
`created_at` timestamp NULL DEFAULT current_timestamp(),
|
|
`updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
PRIMARY KEY (`factory_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 코드 정의 (기존 IssueTypes, error_types 등을 통합 관리)
|
|
CREATE TABLE `code_types` (
|
|
`code_type_id` VARCHAR(50) NOT NULL COMMENT '코드 타입 ID (예: ISSUE_TYPE, ERROR_TYPE)',
|
|
`code_type_name` VARCHAR(100) NOT NULL COMMENT '코드 타입명 (예: 이슈 유형, 에러 유형)',
|
|
`description` TEXT,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`code_type_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE `codes` (
|
|
`code_id` INT NOT NULL AUTO_INCREMENT,
|
|
`code_type_id` VARCHAR(50) NOT NULL COMMENT '참조하는 코드 타입 ID',
|
|
`code_value` VARCHAR(100) NOT NULL COMMENT '코드 값 (예: design_miss)',
|
|
`code_name` VARCHAR(100) NOT NULL COMMENT '코드 표시명 (예: 설계 미스)',
|
|
`code_order` INT DEFAULT 0 COMMENT '정렬 순서',
|
|
`is_active` TINYINT(1) DEFAULT 1,
|
|
`description` TEXT,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`code_id`),
|
|
UNIQUE KEY `uk_code` (`code_type_id`, `code_value`),
|
|
KEY `fk_codes_code_type_id` (`code_type_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
|
|
-- =================================================================
|
|
-- 3. 업무 데이터 (Transactional Data)
|
|
-- =================================================================
|
|
|
|
-- 일일 작업 보고
|
|
CREATE TABLE `daily_work_reports` (
|
|
`report_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`report_date` date NOT NULL COMMENT '작업 날짜',
|
|
`worker_id` int(11) NOT NULL COMMENT '작업자 ID',
|
|
`project_id` int(11) NOT NULL COMMENT '프로젝트 ID',
|
|
`task_id` int(11) NOT NULL COMMENT '작업 ID',
|
|
`work_hours` decimal(4,2) NOT NULL COMMENT '작업 시간',
|
|
`is_error` tinyint(1) NOT NULL DEFAULT 0 COMMENT '에러 여부 (0: 정상, 1: 에러)',
|
|
`error_type_code_id` int(11) DEFAULT NULL COMMENT '에러 유형 코드 ID (codes 테이블 참조)',
|
|
`created_by_user_id` int(11) NOT NULL COMMENT '작성자 user_id',
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
|
|
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
PRIMARY KEY (`report_id`),
|
|
KEY `fk_dwr_worker_id` (`worker_id`),
|
|
KEY `fk_dwr_project_id` (`project_id`),
|
|
KEY `fk_dwr_task_id` (`task_id`),
|
|
KEY `fk_dwr_error_type` (`error_type_code_id`),
|
|
KEY `fk_dwr_created_by` (`created_by_user_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 일일 이슈 보고
|
|
CREATE TABLE `daily_issue_reports` (
|
|
`issue_report_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`report_date` date NOT NULL,
|
|
`worker_id` int(11) NOT NULL,
|
|
`project_id` int(11) NOT NULL,
|
|
`issue_type_code_id` int(11) DEFAULT NULL COMMENT '이슈 유형 코드 ID (codes 테이블 참조)',
|
|
`description` text DEFAULT NULL,
|
|
`start_time` time NOT NULL,
|
|
`end_time` time NOT NULL,
|
|
`created_at` timestamp NULL DEFAULT current_timestamp(),
|
|
PRIMARY KEY (`issue_report_id`),
|
|
KEY `fk_dir_worker_id` (`worker_id`),
|
|
KEY `fk_dir_project_id` (`project_id`),
|
|
KEY `fk_dir_issue_type` (`issue_type_code_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
-- 절단 계획
|
|
CREATE TABLE `cutting_plans` (
|
|
`cutting_plan_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`project_id` int(11) NOT NULL,
|
|
`spec_id` int(11) NOT NULL COMMENT '파이프 사양 ID',
|
|
`drawing_name` varchar(255) NOT NULL,
|
|
`area_number` varchar(100) DEFAULT NULL,
|
|
`spool_number` varchar(255) DEFAULT NULL,
|
|
`length` decimal(10,2) DEFAULT NULL,
|
|
`created_at` timestamp NULL DEFAULT current_timestamp(),
|
|
`updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
PRIMARY KEY (`cutting_plan_id`),
|
|
KEY `fk_cp_project_id` (`project_id`),
|
|
KEY `fk_cp_spec_id` (`spec_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- =================================================================
|
|
-- 외래 키(Foreign Key) 제약조건 설정
|
|
-- =================================================================
|
|
|
|
-- Auth Domain
|
|
ALTER TABLE `users`
|
|
ADD CONSTRAINT `fk_users_worker_id` FOREIGN KEY (`worker_id`) REFERENCES `workers` (`worker_id`) ON DELETE SET NULL;
|
|
ALTER TABLE `login_logs`
|
|
ADD CONSTRAINT `fk_login_logs_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL;
|
|
ALTER TABLE `password_change_logs`
|
|
ADD CONSTRAINT `fk_pw_change_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
|
|
ADD CONSTRAINT `fk_pw_change_changed_by` FOREIGN KEY (`changed_by_user_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL;
|
|
|
|
-- Master Data
|
|
ALTER TABLE `worker_groups`
|
|
ADD CONSTRAINT `fk_w_groups_leader_id` FOREIGN KEY (`group_leader_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
|
|
ADD CONSTRAINT `fk_w_groups_worker_id` FOREIGN KEY (`worker_id`) REFERENCES `workers` (`worker_id`) ON DELETE CASCADE;
|
|
ALTER TABLE `equipment_list`
|
|
ADD CONSTRAINT `fk_equip_factory_id` FOREIGN KEY (`factory_id`) REFERENCES `factory_info` (`factory_id`) ON DELETE SET NULL;
|
|
ALTER TABLE `codes`
|
|
ADD CONSTRAINT `fk_codes_code_type_id` FOREIGN KEY (`code_type_id`) REFERENCES `code_types` (`code_type_id`) ON DELETE CASCADE;
|
|
|
|
-- Transactional Data
|
|
ALTER TABLE `daily_work_reports`
|
|
ADD CONSTRAINT `fk_dwr_worker_id` FOREIGN KEY (`worker_id`) REFERENCES `workers` (`worker_id`) ON DELETE RESTRICT,
|
|
ADD CONSTRAINT `fk_dwr_project_id` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON DELETE RESTRICT,
|
|
ADD CONSTRAINT `fk_dwr_task_id` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`task_id`) ON DELETE RESTRICT,
|
|
ADD CONSTRAINT `fk_dwr_error_type` FOREIGN KEY (`error_type_code_id`) REFERENCES `codes` (`code_id`) ON DELETE SET NULL,
|
|
ADD CONSTRAINT `fk_dwr_created_by` FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`user_id`) ON DELETE RESTRICT;
|
|
ALTER TABLE `daily_issue_reports`
|
|
ADD CONSTRAINT `fk_dir_worker_id` FOREIGN KEY (`worker_id`) REFERENCES `workers` (`worker_id`) ON DELETE RESTRICT,
|
|
ADD CONSTRAINT `fk_dir_project_id` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON DELETE RESTRICT,
|
|
ADD CONSTRAINT `fk_dir_issue_type` FOREIGN KEY (`issue_type_code_id`) REFERENCES `codes` (`code_id`) ON DELETE SET NULL;
|
|
ALTER TABLE `cutting_plans`
|
|
ADD CONSTRAINT `fk_cp_project_id` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON DELETE CASCADE,
|
|
ADD CONSTRAINT `fk_cp_spec_id` FOREIGN KEY (`spec_id`) REFERENCES `pipe_specs` (`spec_id`) ON DELETE RESTRICT;
|
|
|
|
|
|
COMMIT;
|
|
|
|
-- =================================================================
|
|
-- 기존 테이블 (Legacy) - 검토 후 마이그레이션 및 삭제 필요
|
|
-- =================================================================
|
|
/*
|
|
-- 기존 Tasks, IssueTypes, error_types 등은 `codes` 와 `code_types`로 통합 제안
|
|
-- 아래 테이블들은 `codes` 테이블로 데이터 마이그레이션 후 삭제 고려
|
|
CREATE TABLE `IssueTypes` ( ... );
|
|
CREATE TABLE `error_types` ( ... );
|
|
CREATE TABLE `work_status_types` ( ... );
|
|
CREATE TABLE `work_types` ( ... );
|
|
|
|
-- daily_work_reports 로 통합된 것으로 추정되는 레거시 테이블
|
|
CREATE TABLE `WorkReports` ( ... );
|
|
CREATE TABLE `daily_worker_summary` ( ... );
|
|
*/ |