← 기술 블로그

같은 물질, 다른 UUID — CAS 번호로 동의어를 잡아내다

Phase: 8 — 운영 최적화 시리즈: 운영 최적화

이 글을 읽으면 알 수 있는 것

문제: 구연산이 5개

식품첨가물 DB에서 구연산(Citric acid)을 조회하면, UUID가 5개 나옵니다.

UUID-A1  구연산     (B코드 - 보존료)
UUID-A2  구연산     (B코드 - 산도조절제)
UUID-B1  구연산     (E코드 - E330)
UUID-B2  Citric acid (E코드 - 영문 등재)
UUID-C1  구연산     (기타 분류)

원인은 식약처 데이터의 구조에 있습니다. 식품첨가물은 용도별 분류 체계(B코드)와 국제 번호 체계(E코드)에 각각 등재됩니다. 같은 물질이라도 분류 체계가 다르면 별도 행으로 존재합니다. ETL 과정에서 이들을 각각 독립적인 첨가물로 인식하면, UUID가 분류 체계 수만큼 생깁니다.

하나의 제품에 “구연산”이 들어 있을 때, 이 5개 중 어느 UUID에 연결해야 하는가? 사용기준 조회 시 5개를 전부 JOIN해야 하는가? UUID 단편화는 단순한 미관 문제가 아니라, FK 연결과 쿼리 정합성을 위협하는 구조적 문제였습니다.

스코핑: 71건 → 88건

처음에는 단순 집계로 “중복 물질이 71건”이라는 추정치를 얻었습니다. name_ko가 동일한 행을 GROUP BY로 묶은 결과입니다.

SELECT name_ko, COUNT(*) as cnt
FROM additive_use_standard
WHERE name_ko IS NOT NULL
GROUP BY name_ko
HAVING COUNT(*) > 1;
-- 결과: 71건

그런데 이 쿼리에는 두 가지 빠진 것이 있었습니다.

  1. 영문명만 있는 행: name_ko가 NULL이고 name_en만 있는 경우, 한국어 이름이 같은 다른 행과 묶이지 않음
  2. 이명(동의어) 관계: “β-카로틴”과 “카로틴”은 같은 물질이지만 name_ko가 다르므로 GROUP BY에서 별개로 처리됨

서비스 영향 범위를 기준으로 다시 스코핑했습니다. 실제로 FK 연결(제품 → 첨가물 사용기준)에 관여하는 첨가물만 추리고, CAS 번호와 영문명까지 교차 검증한 결과, 88건으로 수정되었습니다.

초기 추정치를 그대로 사용했다면, 17건의 중복을 놓쳤을 것입니다. “빠른 집계”와 “정밀 조사”의 차이가 24%나 나는 상황에서, 추정치를 곧이곧대로 쓰지 않은 것은 맞는 판단이었습니다.

CAS 번호: 이름이 아닌 식별자로 묶기

CAS(Chemical Abstracts Service) 번호는 화학물질에 부여되는 국제 고유 식별자입니다. 물질 이름이 언어·용도·분류 체계에 따라 달라져도, CAS 번호는 변하지 않습니다.

구연산      → CAS 77-92-9
Citric acid → CAS 77-92-9
柠檬酸      → CAS 77-92-9

이 성질을 이용하면, 이름이 다른 행들도 같은 물질인지 판별할 수 있습니다.

CAS 전수 조사 결과

88건 전체에 대해 CAS 번호를 조사했습니다. 결과는 두 그룹으로 나뉩니다.

구분건수비율
CAS 보유63건72%
CAS 미보유25건28%

CAS 보유 63건에 대해 그루핑을 수행하자, name_ko만으로는 발견할 수 없었던 동의어 4쌍이 드러났습니다.

CAS 7235-40-7  → β-카로틴, 카로틴
CAS 9004-34-6  → 결정셀룰로스, 분말셀룰로스
CAS 9000-01-5  → 아라비아검, 아카시아검
CAS 110-15-6   → 숙신산, 호박산

이 4쌍은 name_ko가 완전히 다릅니다. 문자열 비교로는 절대 발견할 수 없고, 사람이 화학 지식을 동원하거나, CAS 번호로 역추적하지 않으면 잡을 수 없는 관계입니다.

두 전략의 트레이드오프

동의어 그루핑에는 두 가지 접근이 있었습니다.

전략 A: name_ko 기반

# name_ko가 같은 행끼리 묶기
groups = defaultdict(list)
for row in additives:
    groups[row.name_ko].append(row)

전략 B: CAS 기반

# CAS 번호가 같은 행끼리 묶기
groups = defaultdict(list)
for row in additives:
    if row.cas_number:
        groups[row.cas_number].append(row)

결론은 양쪽 모두 사용하는 것이었습니다. CAS가 있으면 CAS로 묶고, CAS가 없으면 name_ko로 묶되, CAS가 잡아낸 동의어 4쌍은 수동으로 name_ko 그룹에 머지합니다.

# 최종 전략: CAS 우선 → name_ko 폴백
def build_substance_groups(additives):
    cas_groups = defaultdict(list)
    name_groups = defaultdict(list)
    
    for row in additives:
        if row.cas_number:
            cas_groups[row.cas_number].append(row)
        else:
            name_groups[row.name_ko].append(row)
    
    # CAS 그룹을 마스터로, name_ko 그룹을 보조로
    # CAS가 잡아낸 동의어 쌍은 하나의 그룹으로 통합됨
    return merge_groups(cas_groups, name_groups)

CAS 미보유 25건: 알려진 한계

88건 중 25건은 CAS 번호가 없습니다. 이들은 대부분 혼합물이거나 생물 유래 물질입니다.

# CAS 미보유 예시
곤약                  → 식물 유래 다당류 혼합물
카라기난              → 해조 추출물 (분자량 범위가 넓음)
난소화성말토덱스트린   → 가공 과정에 따라 구조가 달라짐

이 25건에 대해서는 CAS 기반 동의어 탐지가 구조적으로 불가능합니다. 향후 새로운 동의어 관계가 발견되면, 수동으로 등록해야 합니다.

이걸 “나중에 해결하겠다”고 두는 것과, “구조적으로 불가능하다고 문서화”하는 것은 다릅니다. 전자는 잊혀지고, 후자는 다음 사람이 같은 조사를 반복하지 않게 합니다.

# SubstanceMaster 모델에 한계를 명시
class SubstanceMaster(models.Model):
    cas_number = models.CharField(
        max_length=20, null=True, blank=True,
        help_text="CAS 미보유 시 동의어 자동 탐지 불가. "
                  "수동 등록 필요."
    )
    synonym_coverage = models.CharField(
        max_length=10,
        choices=[('cas', 'CAS 검증'), ('manual', '수동 등록')],
        help_text="이 그룹의 동의어가 어떤 방법으로 확인되었는지"
    )

에탄올제제: 접미사 하나가 만든 함정

CAS 조사 중 발견된 사례 하나가 기억에 남습니다.

“에탄올”은 CAS 64-17-5로, DB에 이미 존재합니다. 그런데 “에탄올제제”라는 항목이 별도로 등재되어 있었고, FK 연결이 누락된 상태였습니다.

에탄올     → CAS 64-17-5  (기존 등록, FK 연결 완료)
에탄올제제  → CAS 없음     (별도 등재, FK 미연결)

자동 매칭 로직에서 “에탄올제제”를 새로운 물질로 판단한 이유는 단순합니다. 접미사 “제제”가 붙어 있어서 “에탄올”과 문자열이 일치하지 않았기 때문입니다.

문제는, “에탄올제제”를 위해 새로운 SubstanceMaster 행을 만들려고 했다는 것입니다. DB에 이미 있는 데이터를 새로 만들 뻔한 상황. CAS 전수 조사 과정에서 발견하지 못했다면, 동일 물질에 대한 마스터가 2개 생기는 결과가 되었을 것입니다.

교훈: 접미사(“제제”, “혼합물”, “용액”)는 별도의 정규화 규칙이 필요합니다. 이름 기반 매칭만으로는 이런 변형을 구조적으로 잡을 수 없습니다.

설계: SubstanceMaster ManyToOne 패턴

최종적으로, UUID 단편화를 해소하기 위해 SubstanceMaster 테이블을 설계했습니다.

기존 구조:
Product → AdditiveUseStandard(UUID-A1)
Product → AdditiveUseStandard(UUID-A2)
Product → AdditiveUseStandard(UUID-B1)
  ↑ 같은 구연산인데 3개의 FK

새 구조:
AdditiveUseStandard(UUID-A1) → SubstanceMaster(SM-001)
AdditiveUseStandard(UUID-A2) → SubstanceMaster(SM-001)
AdditiveUseStandard(UUID-B1) → SubstanceMaster(SM-001)
Product → SubstanceMaster(SM-001)
  ↑ 구연산은 하나의 마스터

Django 모델로 표현하면 이런 구조입니다.

class SubstanceMaster(models.Model):
    """
    여러 분류 체계에 흩어진 동일 물질을 하나로 묶는 마스터.
    IC(AdditiveUseStandard) → SubstanceMaster: ManyToOne
    """
    id = models.AutoField(primary_key=True)
    name_ko = models.CharField(max_length=200)   # 대표 한국어명
    name_en = models.CharField(max_length=200, null=True)
    cas_number = models.CharField(max_length=20, null=True)
    
    class Meta:
        db_table = 'substance_master'


class AdditiveUseStandard(models.Model):
    # 기존 필드들 ...
    
    # 새로 추가: 마스터 FK
    substance_master = models.ForeignKey(
        SubstanceMaster,
        on_delete=models.SET_NULL,
        null=True,
        related_name='standards'
    )

이 패턴의 핵심은 기존 테이블을 수정하지 않는다는 것입니다. AdditiveUseStandard의 UUID는 그대로 유지됩니다. SubstanceMaster는 “이 UUID들이 같은 물질이다”라는 관계만 추가합니다.

기존에 AdditiveUseStandard의 UUID로 연결된 FK들은 점진적으로 SubstanceMaster로 전환할 수 있습니다. 빅뱅 마이그레이션이 아니라, 신규 연결부터 마스터를 사용하고 기존 연결은 순차적으로 마이그레이션하는 방식입니다.

최종 결과

항목수치
중복 물질 (최종 확인)88건
CAS 기반 그루핑63건 (72%)
CAS로 발견한 동의어4쌍
CAS 미보유 (수동 관리)25건 (28%)
초기 추정 오차71 → 88 (24% 과소 추정)

교훈