# DB 매핑 구조: 의약품 마스터 ↔ 한약재 코드 테이블

> 의약품 마스터(medicine_master.db)와 기존 한약재 테이블(kdrug.db)은
> **`representative_code`(대표코드)** 로 연결된다.

---

## 1. 테이블 관계도

```
herb_masters (454개 약재, 공통 성분 마스터)
  │  ingredient_code (PK)  예: 3033H1AHM = 계지
  │
  ├─→ herb_products (53,769건, 제품별 코드)
  │     ingredient_code (FK)
  │     representative_code  ← 매핑 키
  │     standard_code
  │     product_code (보험코드 9자리)
  │
  └─→ herb_items (29건, 우리 약국 보유 약재)
        ingredient_code (FK)
        insurance_code
        herb_name  예: "휴먼계지"

medicine_master.db (305,522건, 의약품표준코드 마스터)
  │  representative_code  ← 매핑 키
  │  standard_code
  │  item_std_code (품목기준코드)
  │  product_name, company_name, spec, category, ...
```

---

## 2. 매핑 키: `representative_code` (대표코드)

| 구분 | herb_products | medicine_master |
|------|:---:|:---:|
| 대표코드 수 | 17,356 | 13,771 (한약재) |
| **매칭 수** | **13,188 (95.8%)** | |
| 표준코드 수 | 53,769 | - |
| 표준코드 매칭 | **40,364 (75.1%)** | |

- 풀체인 매칭 약재: **447 / 454개 (98.5%)**
- medicine_master에만 있는 한약재 대표코드: 6개 (무시 가능)
- herb_products에만 있는 대표코드: 4,168개 (취소/폐지 제품 등)

---

## 3. 매핑 예시: 계지

```sql
-- herb_masters: 공통명
ingredient_code = '3033H1AHM', herb_name = '계지'

-- herb_products: 제품들 (57개 제조사)
  휴먼계지     rep: 8800624003904  std: 8800624003911 (500g)
  대효계지     rep: 8800628002200  std: 8800628002224 (500g)
  광명계지     rep: 8800640000505  std: 8800640000512 (500g)
  ...

-- medicine_master: 동일 대표코드로 매칭
  휴먼계지     rep: 8800624003904  item_std: 200406389  category: 한약재
  대효계지     rep: 8800628002200  item_std: 200406525  category: 한약재
  ...
```

---

## 4. 매핑 SQL

### 특정 약재의 전체 제품 + 의약품 마스터 정보 조회
```sql
SELECT hm.herb_name AS 공통명,
       hp.product_name, hp.company_name,
       hp.representative_code, hp.standard_code,
       mm.spec, mm.form_type, mm.package_type,
       mm.item_std_code, mm.category
FROM herb_masters hm
JOIN herb_products hp ON hp.ingredient_code = hm.ingredient_code
LEFT JOIN med_master.medicine_master mm
  ON mm.representative_code = hp.representative_code
  AND (mm.cancel_date IS NULL OR mm.cancel_date = '')
WHERE hm.herb_name = '계지';
```

### herb_items(보유 약재) → 의약품 마스터 연결
```sql
SELECT hi.herb_name, hi.ingredient_code,
       mm.item_std_code, mm.representative_code, mm.standard_code,
       mm.spec, mm.package_type
FROM herb_items hi
JOIN herb_products hp ON hp.ingredient_code = hi.ingredient_code
JOIN med_master.medicine_master mm
  ON mm.representative_code = hp.representative_code
  AND mm.product_name = hi.herb_name
WHERE mm.category = '한약재'
  AND (mm.cancel_date IS NULL OR mm.cancel_date = '');
```

---

## 5. 코드 체계 정리

| 코드 | 출처 | 예시 | 설명 |
|------|------|------|------|
| `ingredient_code` | herb_masters | 3033H1AHM | 성분코드 (약재 공통) |
| `product_code` | herb_products | 062400390 | 보험 제품코드 9자리 |
| `representative_code` | 양쪽 | 8800624003904 | **대표 바코드 (매핑 키)** |
| `standard_code` | 양쪽 | 8800624003911 | 규격별 바코드 |
| `item_std_code` | medicine_master | 200406389 | 식약처 품목기준코드 |
| `insurance_code` | herb_items | 062400390 | = product_code와 동일 체계 |

---

## 6. 활용 가능 시나리오

1. **바코드 스캔 입고**: 제품 바코드(standard_code) → herb_products → ingredient_code → herb_items 자동 매칭
2. **제품 비교**: 같은 ingredient_code의 다른 업체 제품 가격/규격 비교
3. **마스터 검색 → 입고 연동**: 의약품 마스터에서 검색 → representative_code로 herb_products 조회 → 입고 등록
4. **유효성 검증**: 취소(cancel_date) 제품 식별, 현행 유통 제품만 필터링

---

*최종 수정: 2026-02-19*