kdrug-inventory-system/docs/데이터베이스_리팩토링_제안.md

한약재 재고관리 시스템 데이터베이스 리팩토링 제안

📊 현황 분석

1. 한약재제품코드 엑셀 분석 결과

• 전체 데이터: 53,775개 제품
• 주성분코드: 454개 (약재별 고유 코드)
• 업체 수: 128개
• 포장 규격: -, 500g, 600g, 1000g, 1200g, 6000g 등 다양

2. 핵심 발견사항

1. 표준화된 주성분코드 체계 존재

   • 예: 3017H1AHM = 건강
   • 예: 3007H1AHM = 감초
   • 예: 3105H1AHM = 당귀

2. 동일 약재, 다양한 제품

   • 건강: 246개 제품, 70개 업체
   • 감초: 284개 제품, 73개 업체
   • 각 업체마다 고유한 제품명과 제품코드 보유

3. 바코드 시스템

   • 표준코드 (13자리)
   • 대표코드 (13자리)
   • 제품코드 (9자리, 0 포함)

🏗️ 현재 시스템 vs 개선안

현재 시스템 구조

herb_items (약재 마스터)
├── herb_item_id
├── insurance_code
├── herb_name
└── is_active

inventory_lots (재고 로트)
├── lot_id
├── herb_item_id (FK)
├── origin_country
├── unit_price_per_g
└── quantity_onhand

제안하는 개선 구조

1단계: 주성분코드 기반 약재 마스터

-- 약재 마스터 (주성분코드 기준)
CREATE TABLE herb_masters (
    ingredient_code VARCHAR(10) PRIMARY KEY,  -- 예: 3017H1AHM
    herb_name VARCHAR(100) NOT NULL,          -- 예: 건강
    herb_name_hanja VARCHAR(100),             -- 예: 乾薑
    herb_name_latin VARCHAR(200),             -- 예: Zingiberis Rhizoma Siccus
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 제품 마스터 (업체별 제품)
CREATE TABLE herb_products (
    product_id INTEGER PRIMARY KEY AUTOINCREMENT,
    ingredient_code VARCHAR(10) NOT NULL,      -- 주성분코드
    product_code VARCHAR(9) NOT NULL UNIQUE,   -- 9자리 제품코드 (0 포함)
    company_name VARCHAR(200) NOT NULL,        -- 업체명
    product_name VARCHAR(200) NOT NULL,        -- 제품명
    standard_code VARCHAR(13),                 -- 표준코드 (바코드)
    representative_code VARCHAR(13),           -- 대표코드
    package_size VARCHAR(20),                  -- 약품규격(숫자)
    package_unit VARCHAR(20),                  -- 약품규격(단위)
    valid_from DATE,                          -- 적용시작일
    valid_to DATE,                            -- 적용종료일
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ingredient_code) REFERENCES herb_masters(ingredient_code)
);

-- 인덱스 추가
CREATE INDEX idx_product_ingredient ON herb_products(ingredient_code);
CREATE INDEX idx_product_company ON herb_products(company_name);
CREATE INDEX idx_product_barcode ON herb_products(standard_code);

2단계: 재고 관리 개선

-- 재고 로트 (제품별 관리)
CREATE TABLE inventory_lots_v2 (
    lot_id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER NOT NULL,               -- 제품 ID
    lot_no VARCHAR(50),                        -- 로트번호
    origin_country VARCHAR(50),                -- 원산지
    manufacture_date DATE,                     -- 제조일자
    expiry_date DATE,                         -- 유통기한
    received_date DATE NOT NULL,               -- 입고일자
    quantity_onhand DECIMAL(10,2) NOT NULL,    -- 현재고량
    unit_price_per_g DECIMAL(10,2) NOT NULL,   -- 단가
    is_depleted BOOLEAN DEFAULT FALSE,
    supplier_id INTEGER,
    receipt_id INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES herb_products(product_id)
);

🔄 마이그레이션 전략

Phase 1: 기초 데이터 구축

1. 주성분코드 마스터 데이터 임포트

   • 454개 주성분코드와 약재명 매핑
   • 기존 herb_items와 매칭

2. 제품 데이터 임포트

   • 53,775개 제품 데이터 임포트
   • 업체별, 규격별 제품 정보 저장

Phase 2: 기존 데이터 마이그레이션

1. 기존 재고 데이터 매핑

   • 현재 herb_items → 적절한 product_id로 매핑
   • 원산지 정보 유지

2. 입고 내역 연결

   • purchase_receipt_lines → 새로운 product_id 연결

Phase 3: 시스템 전환

1. 바코드 스캔 기능 추가

   • 표준코드로 제품 식별
   • 자동 입고 처리

2. 업체별 제품 선택 UI

   • 약재 선택 시 업체/제품 선택 가능
   • 규격별 재고 관리

💡 장점

1. 표준화

   • 건강보험 급여 코드체계와 일치
   • 업계 표준 바코드 시스템 활용

2. 정확성

   • 업체별, 규격별 정확한 재고 관리
   • 제품 추적성 향상

3. 확장성

   • 새로운 업체/제품 쉽게 추가
   • 바코드 스캔 등 자동화 가능

4. 호환성

   • 외부 시스템과 데이터 교환 용이
   • 도매상 시스템과 연동 가능

🚀 구현 우선순위

즉시 구현 가능

1. herb_masters 테이블 생성 및 데이터 임포트
2. herb_products 테이블 생성 및 데이터 임포트
3. 기존 herb_items에 ingredient_code 컬럼 추가

단계적 구현

1. 입고 시 제품코드/바코드 입력 기능
2. 재고 조회 시 제품별 표시
3. 바코드 스캔 기능 (웹캠 또는 스캐너)

장기 계획

1. 도매상 API 연동
2. 자동 발주 시스템
3. 유통기한 관리

📝 예시 쿼리

건강(乾薑) 제품 조회

SELECT
    p.product_name,
    p.company_name,
    p.package_size || p.package_unit as package,
    p.product_code
FROM herb_products p
JOIN herb_masters m ON p.ingredient_code = m.ingredient_code
WHERE m.herb_name = '건강'
ORDER BY p.company_name, p.package_size;

바코드로 제품 찾기

SELECT
    m.herb_name,
    p.product_name,
    p.company_name
FROM herb_products p
JOIN herb_masters m ON p.ingredient_code = m.ingredient_code
WHERE p.standard_code = '8800680001104';

⚠️ 주의사항

1. 데이터 무결성

   • 제품코드 9자리 유지 (앞자리 0 포함)
   • 날짜 형식 변환 (20201120 → 2020-11-20)

2. 하위 호환성

   • 기존 기능 유지하면서 점진적 마이그레이션
   • 임시로 dual-write 전략 사용 가능

3. 성능 고려

   • 53,775개 제품 데이터 인덱싱 필수
   • 자주 사용하는 쿼리 최적화

---

작성일: 2026-02-15 작성자: Claude Assistant