kdrug-inventory-system/docs/database_erd.md

# 한의원 약재 관리 시스템 ER 다이어그램

## 핵심 엔티티 관계도

```mermaid
erDiagram
    HERB_MASTERS ||--o{ HERB_ITEMS : "has"
    HERB_MASTERS ||--o{ FORMULA_INGREDIENTS : "used_in"
    HERB_ITEMS ||--o{ INVENTORY_LOTS : "has"
    HERB_ITEMS ||--o{ COMPOUND_INGREDIENTS : "used_in"
    HERB_ITEMS ||--o{ HERB_ITEM_TAGS : "has"
    HERB_EFFICACY_TAGS ||--o{ HERB_ITEM_TAGS : "assigned_to"

    FORMULAS ||--o{ FORMULA_INGREDIENTS : "contains"
    FORMULAS ||--o{ COMPOUNDS : "used_for"

    PATIENTS ||--o{ COMPOUNDS : "receives"
    PATIENTS ||--o{ PATIENT_SURVEYS : "completes"

    COMPOUNDS ||--o{ COMPOUND_INGREDIENTS : "contains"
    COMPOUND_INGREDIENTS ||--o{ COMPOUND_CONSUMPTIONS : "consumes"

    INVENTORY_LOTS ||--o{ COMPOUND_CONSUMPTIONS : "consumed_by"
    INVENTORY_LOTS }o--|| SUPPLIERS : "supplied_by"
    INVENTORY_LOTS }o--|| PURCHASE_RECEIPT_LINES : "received_from"

    SUPPLIERS ||--o{ PURCHASE_RECEIPTS : "provides"
    PURCHASE_RECEIPTS ||--o{ PURCHASE_RECEIPT_LINES : "contains"

    STOCK_ADJUSTMENTS ||--o{ STOCK_ADJUSTMENT_DETAILS : "contains"
    STOCK_LEDGER }o--|| HERB_ITEMS : "tracks"
    STOCK_LEDGER }o--|| INVENTORY_LOTS : "tracks"

    HERB_MASTERS {
        string ingredient_code PK
        string herb_name
        string herb_name_hanja
        string herb_name_latin
        text description
        boolean is_active
    }

    HERB_ITEMS {
        int herb_item_id PK
        string ingredient_code FK
        string insurance_code
        string herb_name
        string specification
        boolean is_active
    }

    INVENTORY_LOTS {
        int lot_id PK
        int herb_item_id FK
        int supplier_id FK
        date received_date
        string origin_country
        decimal unit_price_per_g
        decimal quantity_onhand
        date expiry_date
        boolean is_depleted
    }

    FORMULAS {
        int formula_id PK
        string formula_code
        string formula_name
        string formula_type
        int base_cheop
        int base_pouches
        boolean is_active
    }

    FORMULA_INGREDIENTS {
        int ingredient_id PK
        int formula_id FK
        string ingredient_code FK
        decimal grams_per_cheop
        int sort_order
    }

    COMPOUNDS {
        int compound_id PK
        int patient_id FK
        int formula_id FK
        date compound_date
        decimal cheop_total
        decimal pouch_total
        decimal cost_total
        string status
    }

    PATIENTS {
        int patient_id PK
        string name
        string phone
        string gender
        date birth_date
        boolean is_active
    }
```

## 재고 흐름도

```mermaid
flowchart TB
    subgraph 입고
        S[공급처/Suppliers]
        PR[구매영수증/Purchase Receipts]
        PRL[구매영수증라인/Purchase Receipt Lines]
        S --> PR
        PR --> PRL
    end

    subgraph 재고
        HM[약재마스터/Herb Masters]
        HI[약재제품/Herb Items]
        IL[재고로트/Inventory Lots]
        SL[재고원장/Stock Ledger]

        HM --> HI
        HI --> IL
        PRL --> IL
        IL -.-> SL
    end

    subgraph 조제/출고
        F[처방/Formulas]
        FI[처방구성/Formula Ingredients]
        C[조제/Compounds]
        CI[조제구성/Compound Ingredients]
        CC[조제소비/Compound Consumptions]

        F --> FI
        FI -.->|ingredient_code| HM
        F --> C
        C --> CI
        CI --> CC
        CC --> IL
    end

    subgraph 재고조정
        SA[재고조정/Stock Adjustments]
        SAD[조정상세/Adjustment Details]

        SA --> SAD
        SAD --> IL
    end

    IL --> SL
    CC --> SL
    SAD --> SL

    classDef master fill:#e1f5fe
    classDef transaction fill:#fff3e0
    classDef inventory fill:#f3e5f5
    classDef ledger fill:#e8f5e9

    class HM,HI,F master
    class PR,PRL,C,CI,CC transaction
    class IL inventory
    class SL,SA,SAD ledger
```

## 데이터 플로우

### 1. 약재 마스터 데이터 흐름
```
herb_masters (성분코드)
    ↓
herb_items (제품별 재고 단위)
    ↓
inventory_lots (로트별 실재고)
```

### 2. 처방 → 조제 흐름
```
formulas (처방 마스터)
    ↓
formula_ingredients (ingredient_code 기반)
    ↓
compounds (실제 조제)
    ↓
compound_ingredients (제품 선택)
    ↓
compound_consumptions (로트별 소비)
```

### 3. 구매 → 재고 흐름
```
suppliers (공급처)
    ↓
purchase_receipts (영수증)
    ↓
purchase_receipt_lines (라인)
    ↓
inventory_lots (재고 생성)
    ↓
stock_ledger (원장 기록)
```

## 주요 비즈니스 규칙

### 재고 관리
1. **FIFO (선입선출)**: 오래된 로트부터 자동 소비
2. **로트 추적**: 모든 재고는 lot_id로 추적
3. **원산지 관리**: 로트별 origin_country 관리
4. **가격 추적**: 로트별 unit_price_per_g 관리

### 처방 관리
1. **성분코드 기반**: formula_ingredients는 ingredient_code 사용
2. **유연한 제품 선택**: 동일 성분의 다른 제품 선택 가능
3. **2단계 선택**: 약재(마스터) → 제품 → 로트

### 조제 관리
1. **처방 조제**: 등록된 처방 사용
2. **직접 조제**: 처방 없이 직접 구성
3. **가감 가능**: 처방 기본 구성에서 추가/제거 가능

### 원가 계산
```
조제 원가 = Σ(사용량 × 로트별 단가)
```

## 인덱스 전략

### 주요 인덱스
- `herb_items.ingredient_code`: 성분코드 조회
- `inventory_lots.herb_item_id, is_depleted`: 가용 재고 조회
- `inventory_lots.origin_country`: 원산지별 재고 조회
- `formula_ingredients.formula_id`: 처방 구성 조회
- `compounds.patient_id, compound_date`: 환자 조제 이력
- `stock_ledger.herb_item_id, event_time`: 재고 변동 이력

## 데이터 무결성

### 외래키 제약
- `herb_items.ingredient_code` → `herb_masters.ingredient_code`
- `formula_ingredients.ingredient_code` → `herb_masters.ingredient_code`
- `inventory_lots.herb_item_id` → `herb_items.herb_item_id`
- `compound_ingredients.herb_item_id` → `herb_items.herb_item_id`
- `compound_consumptions.lot_id` → `inventory_lots.lot_id`

### 체크 제약
- `inventory_lots.quantity_onhand >= 0`
- `compound_consumptions.quantity_used > 0`
- `purchase_receipt_lines.quantity_g > 0`
- `formula_ingredients.grams_per_cheop > 0`

### 트리거
- 재고 변동 시 `stock_ledger` 자동 기록
- `inventory_lots.quantity_onhand = 0` 시 `is_depleted = 1` 자동 설정