{"id":"cmq4p3x7z006hpl01gphmgs4d","docId":"cmq4nh0vx004hpl01oxeqipfr","version":2,"title":"갭분석 — 기존 ILSA_SHREDDING 스키마 결함과 신스키마 해소 방향","content":"# 기존 `ILSA_SHREDDING` 스키마 결함 정리\n\n> **갱신 v2 (2026-04-30)**: 운영 DB dump 다운로드 후 분석. **운영 = 53 테이블** (ddl.sql 38 + 15 추가). work 도메인 (일일 업무일지) 8 테이블 신 발견.\n\n원본 분석 baseline. 신스키마 `irosafe_shred` 설계 입력.\n\n## 데이터량 단서 (AUTO_INCREMENT)\n\n| 테이블 | 데이터량 | 비고 |\n|---|---:|---|\n| REVINFO | 115,928 | Envers 변경 이력 |\n| SSF_OPERATION_MANAGEMENT | 194,914 | 작업 |\n| SSF_SCHEDULE_MANAGEMENT | 188,538 | 일정 |\n| SSF_APPROVAL_MANAGEMENT | 14,367 | 승인 처리 |\n| SSF_DISPOSAL_MANAGEMENT | 13,336 | 처분 |\n| SSF_SHREDDING_CERTIFICATE | 12,399 | 파쇄증명서 |\n| SSF_ATTACH_FILE | 8,528 | 첨부파일 |\n| SSF_BRANCH_OFFICE_MANAGEMENT | 5,236 | 지점 |\n| SSF_CONSOLE_INFO_MANAGEMENT | 5,343 | 파쇄통(콘솔) |\n| SSF_KAKAO_SENDMESSAGE | 2,968 | 카카오 알림톡 |\n| SSF_MENU_AUTH | 1,703 | 메뉴 권한 |\n| SSF_CONTRACT_RESPONSIBLE_COMPANY | 1,368 | 계약 담당사 |\n| SSF_CONTRACT_MANAGEMENT | 775 | 계약 |\n| SSF_CAR_DISPATCH_MANAGEMENT | 269 | 차량 배차 |\n| SSF_MEMBER | 73 | 사용자 |\n| SSF_CMMN_CODE | 65 | 공통 코드 |\n| SSF_CAR_INFO_MANAGEMENT | 37 | 차량 |\n| SSF_MENU | 25 | 메뉴 |\n| SSF_SUBCONTRACTOR | 10 | 외주업체 |\n| SSF_KAKAO_TEMPLATE | 5 | 카톡 템플릿 |\n| SSF_NOTICE | 3 | 공지사항 |\n\n## 14대 결함\n\n### 1. 날짜/시간 컬럼이 varchar\n- `VISIT_START_DATE varchar(10)`, `VISIT_START_TIME varchar(4)` — 모든 일정 관련 테이블\n- `WRITE_DATE varchar(10)` (NOTICE), `CONTRACT_END_DT varchar(10)` (CONTRACT)\n- **영향**: 인덱스 비효율, 비교 연산 오류 가능, timezone 손실\n- **해결**: `DATE` / `TIME` / `DATETIME(6)` 으로 정상화. 시간대 KST 고정.\n\n### 2. FK constraint 미선언\n- 비즈니스 테이블 간 FK 0개. REVINFO ↔ *_HISTORY 만 FK 존재\n- 누락 예시: `SSF_BRANCH_OFFICE_MANAGEMENT.CONTRACT_SEQ`, `SSF_CONSOLE.BRANCH_OFFICE_SEQ`, `SSF_OPERATION.CONTRACT_SEQ`, `SSF_SCHEDULE.OPERATION_SEQ`, `SSF_DISPOSAL.SCHEDULE_SEQ`, `SSF_CERTIFICATE.SCHEDULE_SEQ`\n- **영향**: 데이터 무결성 미보장. 고아 row 누적 가능\n- **해결**: 모든 SEQ 참조에 `FOREIGN KEY` 명시 + ON DELETE/UPDATE 정책\n\n### 3. 비정규화 (지점 정보 중복)\n- `SSF_OPERATION_MANAGEMENT` / `SSF_SCHEDULE_MANAGEMENT` / `SSF_SHREDDING_CERTIFICATE` 에 `BRANCH_OFFICE_NAME`, `BRANCH_OFFICE_PHONE_NUMBER`, `BRANCH_OFFICE_MANAGER_NAME`, `ADDRESS` 중복 저장\n- **영향**: 지점명 변경 시 모든 관련 row 갱신 필요. 일관성 깨질 위험\n- **해결**: SEQ 외래키만 보유. 실제 정보는 JOIN. 필요시 view 또는 캐시 컬럼.\n\n### 4. ID/SEQ 이중 식별자\n- `SSF_SUBCONTRACTOR`: `SUBCONTRACTOR_SEQ` (PK, int) + `SUBCONTRACTOR_ID` (varchar(20))\n- 동일 패턴: `BRANCH_OFFICE_SEQ` + `BRANCH_OFFICE_ID`, `CONTRACT_SEQ` + `CONTRACT_ID`, `MEMBER_SEQ` + `MEMBER_ID`\n- 다른 테이블에서 둘 다 외래키로 사용 (`CONSOLE` 은 `BRANCH_OFFICE_SEQ` + `BRANCH_OFFICE_ID` 둘 다 보유)\n- **영향**: 데이터 일관성 깨질 가능성. JPA 엔티티 혼란\n- **해결**: 단일 대리키 `bigint id` 만 PK로. 자연키 (사업자번호, 사용자 로그인 ID) 는 별도 unique 컬럼.\n\n### 5. 상태값 매직코드\n- `STATUS varchar(8) DEFAULT 'C1320000'` — 의미 알기 위해 SSF_CMMN_CODE 조회 필요\n- `CRUSH_OPTION varchar(1)` ('S'=현장파쇄, 'W'=입고파쇄), `REPETITION_CYCLE varchar(1)` ('M'=월/'W'=주), `REPETITION_FORM varchar(1)` ('A'=요일/'B'=일)\n- **영향**: 코드 가독성 0, 디버깅 어려움\n- **해결**: `ENUM` (MariaDB) 또는 의미 있는 lookup 테이블. ENUM 권장 (가독성·인덱스).\n\n### 6. 감사 컬럼 일관성 없음\n- 모든 테이블 `INS_DATE/INS_ID/UPD_DATE/UPD_ID` 보유하나 `DEL_DATE/DEL_YN`은 일부만\n- `BRANCH_OFFICE`, `CERTIFICATE` 만 soft delete 패턴. 다른 테이블은 hard delete\n- **영향**: 삭제 정책 통일성 X\n- **해결**: BaseEntity 추출. 모든 테이블 동일 컬럼 (`created_at, created_by, updated_at, updated_by, deleted_at, deleted_by`). 사용자는 `bigint user_id` FK로.\n\n### 7. M:N 관계 테이블 PK/FK 없음\n- `SSF_CONTRACT_MANAGEMENT_SSF_BRANCH_OFFICE_MANAGEMENT`: 두 컬럼만, PK 없음, FK 없음 (Hibernate `@ManyToMany` 자동 생성)\n- `SSF_CAR_INFO_MANAGEMENT_SSF_CAR_DISPATCH_MANAGEMENT`: 비슷\n- **영향**: 중복 row 가능, 무결성 X\n- **해결**: 명시적 매핑 엔티티. PK + FK + UNIQUE constraint. 또는 1:N 으로 단순화 (배차는 차량 1개에 종속이면 직접 FK).\n\n### 8. Envers 감사 폭증\n- REVINFO `AUTO_INCREMENT=115,928` — 5년치\n- 8개 `*_HISTORY` 테이블에 모든 컬럼 + `_MOD bit(1)` 매 변경마다 row 적재\n- **영향**: HISTORY 누적, 쿼리 성능 영향\n- **해결**: 감사 정말 필요한 5~7개 테이블만 (`subcontractor`, `contract`, `branch_office`, `member`, `console`, `certificate`). 나머지는 audit 컬럼만. 또는 별도 audit 시스템 (debezium → kafka → archive).\n\n### 9. MENU / MENU_AUTH 컬럼 중복\n- `SSF_MENU` 와 `SSF_MENU_AUTH` 둘 다 `MENU_NM`, `MENU_NO`, `MENU_URL`, `MENU_DEPTH`, `MENU_ORDER`, `UPPER_MENU_NO` 중복 보유\n- **영향**: 메뉴 정보 변경 시 두 테이블 갱신\n- **해결**: `menu_auth` 는 `menu_id` (FK) + `member_id` 만 보유. 메뉴 정보는 JOIN.\n\n### 10. 운영 DDL에 샘플 테이블\n- `SSF_SAMPLE` (샘플 시퀀스, 샘플 내용, 샘플 제목, 상태 'C2010000') — 운영 코드에 잔존\n- **해결**: 신스키마에서 제거.\n\n### 11. 컬럼 코멘트 오류\n- `SSF_KAKAO_TEMPLATE.USE_CHANNEL` 코멘트 = `'템플릿 명'` (잘못)\n- `SSF_KAKAO_SENDMESSAGE.TMPLT_CODE` 코멘트 = `'수신자번호'` (잘못)\n- `SSF_KAKAO_SENDMESSAGE.UID` 코멘트 = `'발신자'` (실제로는 메시지 UID 추정)\n- **해결**: 신스키마에서 정확한 코멘트로. 도메인 용어집 부록.\n\n### 12. DISPOSAL ↔ CONSOLE 데이터 복제\n- `SSF_DISPOSAL_MANAGEMENT` 가 `CONSOLE_SEQ` 와 함께 `CRUSHER_BAG_BARCODE`, `CRUSHER_STATUS`, `CURRENT_MATCH_NO`, `BRANCH_OFFICE_NAME`, `BRANCH_OFFICE_MANAGER_NAME` 중복 저장\n- **의미**: DISPOSAL 은 CONSOLE 의 상태 변경 이벤트 (자루 회수·파쇄 완료) 라 콘솔 정보를 그대로 복제\n- **해결**: `disposal` 은 `console_id` FK + 이벤트 시점 상태(`crusher_status_at`, `barcode_at`) + 시각만 보유. 콘솔 정보는 JOIN.\n\n### 13. 공통 코드 인접 리스트 한계\n- `SSF_CMMN_CODE` 의 `UPPER_CODE` + `DEPTH` 로 트리 구성 — 인접 리스트 모델\n- **영향**: 깊이 검색 비효율 (재귀 SQL), 트리 변경 비용\n- **해결**: 공통 코드는 깊이 2 이내라 큰 문제 X. ENUM 으로 대체 가능한 코드는 ENUM 으로 빼고, 트리 필요한 항목만 별도 lookup. 또는 closure table.\n\n### 14. 자잘한 컬럼 정의 오류\n- `BUSSINESS_NUMBER` 오타 (`BUSINESS`)\n- `QUANTITY varchar(50)` (수량인데 문자열 50자) → `INT` 또는 `DECIMAL`\n- `FEE decimal(21,0)` 21자리 정수 → `DECIMAL(15,0)` 충분\n- `MEMBER.PASSWORD varchar(256)` — bcrypt 해시 저장이면 60자 충분, 또는 BCrypt 명시\n- `LOGIN_FAIL_CNT` 코멘트 `'로그인 실팻 횟수'` (오타)\n- `serverTimezone=Aisa/Seoul` 오타 (Asia → Aisa) — application.yml dev 쪽\n- **해결**: 컬럼명·타입·길이 정상화. 보안 컬럼(password) 은 별도 정책.\n\n## 운영 DB 신 발견 결함 (코드 ddl.sql 에 없던 테이블)\n\n운영 백업 dump (2026-04-30) 분석 결과 코드 ddl.sql 38테이블 외 **15테이블 추가** 발견. 특히 **work 도메인 (일일 업무일지) 8테이블** 이 모두 결함 가득.\n\n### 신 발견 테이블\n\n| 테이블 | 데이터량 | 도메인 |\n|---|---:|---|\n| SSF_WORK_MANAGEMENT | 2,588 | 업무일지 (작업일·차량·운행담당자·하차장소) |\n| SSF_WORK_JOB_MANAGEMENT | 45,093 | 업무에 포함된 작업들 (스케쥴·증명서 매핑) |\n| SSF_WORK_MEMBER_MANAGEMENT | 4,230 | 근무자·시작·종료·인건비 |\n| SSF_WORK_PAPER_MANAGEMENT | 1,038 | 파지량·급지종류·단가 |\n| SSF_WORK_EXPENDITURE_MANAGEMENT | 13,315 | 경비·결제방법·금액 |\n| SSF_WORK_FEEDBACK_MANAGEMENT | 1,099 | 피드백 메모 |\n| SSF_WORKTYPE_MANAGEMENT | 4,355 | 작업타입·중량 (증명서 단위) |\n| SSF_SCHEDULE_MANAGER_INFO | 322,905 | 일정 담당자 정보 |\n| SSF_MENU_BAK | - | 메뉴 백업 (운영 잔존) |\n| + 8개 *_HISTORY | - | 위 audit |\n\n→ work 도메인 = 운영팀 매일 업무일지 시스템. 정산·인건비·세금계산 베이스.\n\n### 15. work 도메인 — 모든 컬럼 `varchar(100)`\n- `WORK_DATE varchar(100)`, `JOB_WORK_DAY varchar(100)` — 날짜에 100자\n- `WORK_MEMBER_START_TIME / END_TIME varchar(100)` — 시각 100자\n- `WORK_MEMBER_ID varchar(100)`, `INS_ID varchar(100)`, `UPD_ID varchar(100)` — 사용자 ID 100자 (다른 도메인은 varchar(20))\n- 일관성 없음 — 같은 사용자 ID 가 다른 테이블엔 varchar(20) 다른 곳 varchar(100)\n- **해결**: 신스키마에서 `created_by BIGINT` (member.id FK) + `WORK_DATE` → `DATE` + 시각 → `TIME`\n\n### 16. work 도메인 FK 미선언 — 0개\n- `WORK_SEQ`, `JOB_SCHEDULE_SEQ`, `JOB_SHREDDING_SEQ`, `SHREDDING_SEQ`, `WORK_CAR_SEQ` 모두 외래키 정의 없음\n- 데이터 무결성 보장 X — 고아 row 누적 가능\n- **해결**: `FOREIGN KEY ... ON DELETE RESTRICT` 명시\n\n### 17. 금액·수량·작업량 문자열 — `DECIMAL` 이어야\n- `EXPENDITURE_PRICE varchar(100)` (경비 금액)\n- `JOB_SERVICE_PRICE varchar(100)` (서비스 비용)\n- `JOB_WEIGHT varchar(100)` / `WORK_WEIGHT varchar(255)` / `PAPER_WEIGHT varchar(100)` (중량)\n- `JOB_AMOUNT varchar(100)` (수량)\n- `PAPER_PRICE varchar(100)` (단가)\n- `WORK_MEMBER_PRICE varchar(100)` (인건비)\n- `paperTotal varchar(255)` (단가 합계)\n- **영향**: 정산·세금계산 시 문자열 → 숫자 변환 부하 + 계산 오류 위험\n- **해결**: `DECIMAL(15,2)` (금액) / `DECIMAL(10,3)` (중량) / `INT` (수량)\n\n### 18. work 도메인 비정규화\n- `JOB_BRANCH_NAME varchar(100)`, `JOB_CONTRACT_NAME varchar(100)`, `FEEDBACK_CONTRACT_NAME varchar(100)` — 지점/거래처 이름 중복 저장\n- `WORK_PAPER_PLACE varchar(100)` (하차장소 업체명) — 거래처 FK 가능\n- **해결**: SEQ FK 만 보유, 이름은 JOIN\n\n### 19. work 도메인 컬럼명 일관성 X\n- `paperTotal varchar(255)` (camelCase)\n- `WORK_DATE varchar(100)` (SCREAMING_SNAKE)\n- 같은 테이블 안에 두 컨벤션 혼재 (`SSF_WORK_PAPER_MANAGEMENT.paperTotal` + `PAPER_TYPE`)\n- **해결**: 신스키마에서 모두 `snake_case` 통일\n\n### 20. work 도메인 - 차량번호 컬럼 분할\n- `SSF_WORK_MANAGEMENT.WORK_CAR_NUM_1`, `WORK_CAR_NUM_2` — 차량 두 대 의미\n- 배열을 컬럼 분할 → 정규화 위반\n- 만약 더 많은 차량 필요하면? 컬럼 추가 무한반복\n- **해결**: `work_car (work_id FK, car_id FK, sequence INT)` 별도 테이블 또는 1:N 매핑\n\n## 운영 데이터 SELECT 분포 결함 (2026-04-30 DB 호스트 검증)\n\n### 21. `contract.STATUS` 의미 혼합 ⚠️\n같은 컬럼에 두 다른 도메인 코드 섞여 저장:\n```\nC2020000 (승인 — C2XX 승인상태)        36\nC2030000 (반려 — C2XX 승인상태)        1\nC4010000 (계약 — C4XX 계약상태)        237\nC4020000 (계약만료 — C4XX 계약상태)    2407\nC4030000 (계약해지 — C4XX 계약상태)    101\n```\n→ 컬럼 1개에 두 도메인 의미 충돌. **해결**: 신스키마에서 `contract.status` (계약상태) + `contract.approval_status` (승인상태) 분리.\n\n### 22. `member.MEMBER_LEVEL` 매직코드 직접 저장 ⚠️\n운영 데이터: `C3010000`/`C3020000`/`C3030000` 그대로 저장. CODE_VALUE (`SUPER`/`ADMIN`/`USER`) 미사용. **마이그레이션 ETL** 시 변환 매핑 필수.\n\n### 23. `work_job.JOB_PAY_TYPE` 카오스 (6종 표현 혼재)\n```\nNULL              10,250\n'' 빈문자열       34,284 (90%)\n'card'            432\n'duty'            55\n'null' 문자열     65\n'카드'            3\n'카드결재'        1\n```\n→ 영어/한국어/빈/null/매직 모두. 신스키마 ENUM 변환 시 `EMPTY|NULL` → `UNSPECIFIED`, `'card'|'카드'|'카드결재'` → `CARD`, `'duty'` → `OFFICIAL` 매핑 ETL 필요.\n\n### 24. `console.CRUSHER_STATUS` 일관성 X\n```\nNULL                49\n'' 빈문자열         624\nC1100000 (카테고리) 1   ← 자식 코드여야 하는 위치에 카테고리 코드 잘못 저장\nC1120000 (수거)     3,676\n```\n→ ETL 시 `C1100000` row 1건 별도 처리 필요. 나머지는 `EMPTY|NULL` → `UNKNOWN`, `C1120000` → `COLLECTED`.\n\n### 25. 도메인 간 컬럼명·값 일관성 0\n- `work_job.JOB_PAY_TYPE` = 영어 (`card`, `duty`) + 매직코드 + 빈값\n- `work_expenditure.EXPENDITURE_PAY_TYPE` = 한국어 (`카드`, `현금`, `기타`)\n- `member.MEMBER_LEVEL` = CMMN_CODE 매직값 (`C3010000`)\n- `contract.STATUS` = 의미 혼합 (C2X + C4X)\n→ 도메인별 결정 일관성 X. **신스키마: 모든 ENUM 영문 대문자 SCREAMING_SNAKE_CASE 통일**.\n\n### 26. `branch_office.STATUS` redundancy\n```\nNULL: 7 / C4010000 4327 / C4020000 2671 / C4030000 828\n```\nbranch_office 가 자체 상태 없이 **contract.status 따라가는 데이터**. 신스키마에서는 branch_office 자체 상태 (예: 활성/방문중지/해지) 별도 정의. contract 와 별도 라이프사이클.\n\n### 27. `certificate` 미발급률 50% — 비즈니스 정합성 이슈\n```\nC9010000 발급:    43,940\nC9020000 미발급:  44,624\n```\n→ 50% 가까이 미발급. 신스키마 마이그 시 미발급 row 들이 진짜 미발급인지, 발급 후 status 갱신 누락인지 운영팀 확인 필요.\n\n### 28. `CERTIFICATE_TYPE` 컬럼 두 곳, 의미 다름\n- `contract.CERTIFICATE_TYPE` = 계약 시 약속한 양식 (단건 90%, 연간 8%)\n- `certificate.CERTIFICATE_TYPE` = 실 발급 양식 (연간 90%, 단건 3%, 코웨이 6%)\n→ 같은 컬럼명 두 다른 의미. **신스키마 분리**:\n  - `contract.contracted_template_id` FK→certificate_template.id\n  - `certificate.template_id` FK→certificate_template.id (실 발급 시점 결정)\n\n### 29. `worktype.WORK_TYPE` 종류·단위 혼재\n```\nbox (1565)       ← 종류 (박스 단위)\nA4box (1218)     ← 종류 (A4 박스)\nHDD (59)         ← 종류 (HDD)\nkg (651)         ← 단위 (무게)\nmadae (106)      ← 종류 (마대)\n```\n→ 신스키마 분리: `unit_type ENUM('BOX','A4_BOX','MADAE','HDD','LOOSE')` + `weight DECIMAL(10,3)` 별도 (kg 단위는 weight 측정 시 사용).\n\n### 30. `JOB_ANNUAL = 'Y'/'N'` BOOLEAN 변환\n운영 분포: Y(43879)/N(1211). 일관 → BOOLEAN 변환 단순. 결함이라기보단 **마이그레이션 ETL 노트**.\n\n## 결함 총합 (v3): 30개\n\n- **A. 데이터 타입**: 1, 14, 15, 17, 18, 30\n- **B. 정합성/FK**: 2, 7, 12, 16, 19, 26\n- **C. 정규화**: 3, 9, 19, 20, 28\n- **D. 식별자/명명**: 4, 14, 19, 25\n- **E. 상태값/매직코드**: 5, 11, 21, 22, 23, 24, 25, 27, 29\n- **F. 감사/스키마 부산물**: 6, 8, 10\n- **G. 트리/계층**: 13\n\n## 추가 관찰\n\n### Envers 의존도\n- `spring-data-envers` 가 운영 데이터 신뢰의 근거 중 하나. 신스키마에서 audit 정책 명확히 정의 후 decommission 또는 축소.\n\n### 외래키 자연 정의 (현 코드/도메인 기준)\n```\nsubcontractor (1) ←→ (N) contract\ncontract (1) ←→ (N) branch_office\nbranch_office (1) ←→ (N) console\ncontract (1) ←→ (N) operation\noperation (1) ←→ (N) schedule\nschedule (1) ←→ (N) disposal\nschedule (1) ←→ (1) certificate\nconsole (1) ←→ (N) disposal\ncar (N) ←→ (1) subcontractor\ncar (1) ←→ (N) car_dispatch\ncar_dispatch (N) ←→ (1) branch_office\nmember (N) ←→ (1) subcontractor\nmember (1) ←→ (N) menu_auth\nmenu (1) ←→ (N) menu_auth\n```\n\n이 관계는 신스키마에서 모두 FK 로 명시.\n","sourceHash":"0a563bdde784f0a96198b7bf99c79446","archivedAt":"2026-06-08T13:13:08+09:00","archivedBy":"sync"}