# Goalskill 다국어(i18n) 전략

> 작성일: 2026-04-28
> 목적: Python + 기본정보기술자 콘텐츠를 일본어 외 언어(한국어/영어 등)로 확장할 때의 DB 설계 및 AI 대화 처리 전략 정리

---

## 1. 결론 한 줄

> **콘텐츠는 `_i18n` 사이드 테이블로 미리 번역 저장, AI 대화는 시스템 프롬프트에 언어를 지정해 처음부터 해당 언어로 답하게 한다. 번역 API는 "과거 메시지 보기" 같은 보조 기능에만 사용.**

---

## 2. 일반적인 i18n DB 패턴 비교

| 방식 | 구조 | 장점 | 단점 |
|---|---|---|---|
| ① 언어별 테이블 | `cert_questions`, `cert_questions_kr`, `cert_questions_en` | 쿼리 단순 | **스키마 드리프트 위험** (한쪽만 컬럼 추가/오류), 마이그레이션 N배, 코드 분기 필요, 언어 추가 = 테이블 신설 |
| ② 컬럼 분리 | `question_text_ja`, `question_text_kr`, `question_text_en` | 한 행에 다 있음 | 컬럼 폭발, 언어 추가 = ALTER TABLE, NULL 많음 |
| **③ 번역 사이드 테이블 (★권장)** | `cert_questions`(공통) + `cert_questions_i18n(question_id, lang, ...)` | **언어 추가가 INSERT만으로 가능**, 공통 데이터(이미지, 정답 등)는 1곳, 누락 언어 fallback 쉬움 | JOIN 1회 추가 |
| ④ lang 컬럼 + 행 중복 | `cert_questions(id, chapter, q_num, lang, ...)` 복합 PK | 단일 테이블 | 공통 데이터 중복 저장 |

---

## 3. 데이터 분류 (현재 SQL 분석 결과)

### A. 번역 필요 콘텐츠 테이블

| 테이블 | DB | 번역 대상 컬럼 |
|---|---|---|
| `cert_questions` | C_DB | `question_text`, `option_1~4`(텍스트일 때), `solution_text` |
| `section_1` ~ `section_22` | Theory_DB | `text` (해설 본문) |
| `master_items` | C_DB | `name` (커리큘럼 항목명) |
| `cert_elements` / `pjt_elements` | C_DB | `name`, `description` |

### B. 번역 불필요

- **사용자별 데이터**: `chat_messages`, `user_curriculum`, `user_category_progress`, `user_profile_summary`, `findgoal_*`, `mbti_*`, `spi_*` 등 → 사용자 입력이라 원본 그대로 저장
- **언어 무관 데이터**: `monthly_blocks`, 정답(ア/イ/ウ/エ), 이미지 경로, FK 등

---

## 4. 추천 구조 — 사이드 테이블 패턴

### 4-1. cert_questions (대표 예시)

```
[BEFORE — 지금 구조]
┌─────────────────────────────────────────────────────────────┐
│ cert_questions                                              │
├──────┬─────────┬───────┬──────────────┬──────────┬──────────┤
│ id   │ chapter │ q_num │ question_text│ option_1 │ correct  │
├──────┼─────────┼───────┼──────────────┼──────────┼──────────┤
│ 100  │   2     │  2-5  │ 論理式 X=... │  ...png  │   ア     │
└──────┴─────────┴───────┴──────────────┴──────────┴──────────┘
                         ↑ 일본어만 저장됨


[AFTER — 추천 구조]
┌─────────────────────────────────────────┐
│ cert_questions (공통 정보)              │
├──────┬─────────┬───────┬─────────┬──────┤
│ id   │ chapter │ q_num │ q_img   │correct│
├──────┼─────────┼───────┼─────────┼──────┤
│ 100  │   2     │  2-5  │ ...png  │  ア  │
└──────┴─────────┴───────┴─────────┴──────┘
        ↓ JOIN
┌────────────────────────────────────────────────┐
│ cert_questions_i18n (언어별 번역)              │
├─────────────┬──────┬──────────────┬───────────┤
│ question_id │ lang │ question_text│ option_1  │
├─────────────┼──────┼──────────────┼───────────┤
│  100        │  ja  │ 論理式 X=... │  ...      │
│  100        │  kr  │ 논리식 X=... │  ...      │
│  100        │  en  │ The logic... │  ...      │
└─────────────┴──────┴──────────────┴───────────┘
```

### 4-2. ERD (Mermaid)

```mermaid
erDiagram
    cert_questions ||--o{ cert_questions_i18n : translates
    cert_questions {
        int id PK
        int chapter
        varchar question_number
        text question_img "공통"
        varchar correct_answer "공통"
        text solution_img "공통"
    }
    cert_questions_i18n {
        int question_id PK,FK
        varchar lang PK "ja/kr/en"
        text question_text
        text option_1
        text option_2
        text option_3
        text option_4
        text solution_text
    }

    master_items ||--o{ master_items_i18n : translates
    master_items {
        int item_id PK
        int block_id
        varchar category
        varchar item_type
        int sort_order
    }
    master_items_i18n {
        int item_id PK,FK
        varchar lang PK
        varchar name
    }
```

---

## 5. master_items / user_curriculum — 특별 케이스

### 핵심 포인트

`user_curriculum`은 **이름을 저장하지 않고 `item_id`만 참조**합니다. 따라서 `master_items`만 다국어화하면 모든 사용자에게 자동 적용됩니다.

```
master_items (마스터 — 1번만 다국어화)
    ↓ item_id로 참조
user_curriculum (유저별 — FK만 저장)
    ↓ JOIN으로 이름 가져옴
화면 표시
```

### 구조

```
┌──────────────────────────────────┐
│ master_items (공통 정보)         │
├─────────┬──────────┬─────────────┤
│ item_id │ block_id │ category    │
│   12    │    7     │ CERT        │
└─────────┴──────────┴─────────────┘
        ↓ 1:N
┌──────────────────────────────────┐
│ master_items_i18n (언어별 이름)  │
├─────────┬──────┬─────────────────┤
│ item_id │ lang │ name            │
├─────────┼──────┼─────────────────┤
│   12    │  ja  │ 第2章 集合と... │
│   12    │  kr  │ 2장 집합과...   │
│   12    │  en  │ Ch.2 Sets...    │
└─────────┴──────┴─────────────────┘

┌────────────────────────────────────────┐
│ user_curriculum (유저별 — 변경 불필요) │
├────┬────────────┬─────────┬────────────┤
│ id │ session_id │ item_id │ status     │
├────┼────────────┼─────────┼────────────┤
│ 1  │ sess_xxx   │   12    │ COMPLETED  │  ← item_id만 저장
└────┴────────────┴─────────┴────────────┘
```

### 조회 SQL 변화

**현재 (단일 언어)**
```sql
SELECT uc.*, mi.name
FROM user_curriculum uc
JOIN master_items mi ON uc.item_id = mi.item_id
WHERE uc.session_id = ?
```

**변경 후 (다국어 + 폴백)**
```sql
SELECT uc.*,
       COALESCE(mii.name, mi_ja.name) AS name  -- 사용자 언어 없으면 일본어 fallback
FROM user_curriculum uc
JOIN master_items mi
     ON uc.item_id = mi.item_id
LEFT JOIN master_items_i18n mii
     ON mii.item_id = mi.item_id
    AND mii.lang = ?              -- 사용자 언어
LEFT JOIN master_items_i18n mi_ja
     ON mi_ja.item_id = mi.item_id
    AND mi_ja.lang = 'ja'         -- 폴백
WHERE uc.session_id = ?
```

### 장점

1. **유저 데이터는 손대지 않음** — `user_curriculum`, `user_category_progress` 그대로
2. **유저가 언어 바꿔도 진도 유지** — `item_id`로 묶여있어서 한국어로 본 진도가 그대로 영어로 보일 뿐
3. **마스터 1번 번역하면 전 유저 적용** — 새 유저 가입 시 즉시 해당 언어 커리큘럼 제공
4. **이름 수정도 1곳만** — 일본어 표기 수정 시 다른 언어는 영향 X

---

## 6. AI 대화 번역 전략

### 결론
**번역 API를 매 메시지마다 호출하지 않습니다.** 비효율, 느림, 캐릭터 톤 깨짐.

### ❌ 잘못된 방식 (Gemini → 번역 API)

```
사용자(KR): "안녕"
   ↓
Gemini 응답(JP): "こんにちは！..."
   ↓
번역 API 호출: ja → kr     ← 추가 비용·지연·품질 저하
   ↓
사용자에게: "안녕하세요!..."
```

문제: API 호출 2회, 응답 느림, 어색한 직역, Tobu 캐릭터 톤 손상

### ⭕ 올바른 방식 (Gemini가 처음부터 해당 언어로)

```
사용자(KR): "안녕"
   ↓
시스템 프롬프트 끝에 추가:
  "ユーザーの言語: 한국어. すべての応答を韓国語で行うこと。"
   ↓
Gemini 응답(KR): "안녕하세요!..."   ← 1번 호출로 끝
```

장점: API 1회, 빠름, Tobu 캐릭터 톤 유지, 자연스러운 한국어

### 데이터 종류별 처리 매트릭스

| 데이터 종류 | 처리 방법 | 시점 |
|---|---|---|
| **UI 라벨** ("選択肢を選んでください" 등) | 프론트 i18n JSON 파일 (`i18next` 등) | 정적 |
| **DB 콘텐츠** (문제, 커리큘럼 항목명) | `_i18n` 테이블에 미리 번역 저장 | 사전 (1회) |
| **AI 동적 응답** (Tobu 대화) | Gemini 시스템 프롬프트에 언어 지정 | 실시간, 1회 호출 |
| **과거 일본어 채팅 다시 보기** | "번역" 버튼 클릭 시에만 번역 API | 온디맨드 |

### Gemini 시스템 프롬프트 예시

```python
# back/app/core/prompts.py 등
def get_tobu_prompt(user_lang: str = 'ja'):
    base = load_tobu_persona()  # 기존 Tobu 페르소나
    lang_directive = {
        'ja': '日本語で応答する。',
        'kr': '한국어로 응답한다. 페르소나 톤은 그대로 유지.',
        'en': 'Respond in English. Keep the persona tone.'
    }[user_lang]
    return f"{base}\n\n# 言語設定\n{lang_directive}"
```

---

## 7. 마이그레이션 SQL 초안

### 7-1. cert_questions

```sql
USE C_DB;

CREATE TABLE `cert_questions_i18n` (
  `question_id`    INT NOT NULL,
  `lang`           VARCHAR(5) NOT NULL,    -- 'ja', 'kr', 'en'
  `question_text`  TEXT,
  `option_1`       TEXT,
  `option_2`       TEXT,
  `option_3`       TEXT,
  `option_4`       TEXT,
  `solution_text`  TEXT,
  PRIMARY KEY (`question_id`, `lang`),
  CONSTRAINT `fk_cqi_question`
    FOREIGN KEY (`question_id`) REFERENCES `cert_questions` (`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 기존 데이터를 일본어로 이관
INSERT INTO `cert_questions_i18n`
  (question_id, lang, question_text, option_1, option_2, option_3, option_4, solution_text)
SELECT id, 'ja', question_text, option_1, option_2, option_3, option_4, solution_text
FROM `cert_questions`;
```

### 7-2. master_items

```sql
USE C_DB;

CREATE TABLE `master_items_i18n` (
  `item_id` INT NOT NULL,
  `lang`    VARCHAR(5) NOT NULL,
  `name`    VARCHAR(200) NOT NULL,
  PRIMARY KEY (`item_id`, `lang`),
  CONSTRAINT `fk_mi_i18n_item`
    FOREIGN KEY (`item_id`) REFERENCES `master_items` (`item_id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `master_items_i18n` (item_id, lang, name)
SELECT item_id, 'ja', name FROM `master_items`;
```

### 7-3. cert_elements / pjt_elements

```sql
USE C_DB;

CREATE TABLE `cert_elements_i18n` (
  `element_id`  INT NOT NULL,
  `lang`        VARCHAR(5) NOT NULL,
  `description` TEXT,
  PRIMARY KEY (`element_id`, `lang`),
  CONSTRAINT `fk_cei_element`
    FOREIGN KEY (`element_id`) REFERENCES `cert_elements` (`element_id`)
    ON DELETE CASCADE
);

CREATE TABLE `pjt_elements_i18n` (
  `element_id`  INT NOT NULL,
  `lang`        VARCHAR(5) NOT NULL,
  `name`        VARCHAR(200) NOT NULL,
  `description` TEXT,
  PRIMARY KEY (`element_id`, `lang`),
  CONSTRAINT `fk_pei_element`
    FOREIGN KEY (`element_id`) REFERENCES `pjt_elements` (`element_id`)
    ON DELETE CASCADE
);

INSERT INTO `cert_elements_i18n` (element_id, lang, description)
SELECT element_id, 'ja', description FROM `cert_elements`;

INSERT INTO `pjt_elements_i18n` (element_id, lang, name, description)
SELECT element_id, 'ja', name, description FROM `pjt_elements`;
```

### 7-4. section_1 ~ section_22 (Theory_DB)

22개 섹션이 동일 구조라 공통 패턴으로 처리 가능:

```sql
USE Theory_DB;

-- 섹션마다 반복 (예시는 section_1)
CREATE TABLE `section_1_i18n` (
  `section_id` INT NOT NULL,
  `lang`       VARCHAR(5) NOT NULL,
  `text`       TEXT,
  PRIMARY KEY (`section_id`, `lang`),
  CONSTRAINT `fk_s1i_section`
    FOREIGN KEY (`section_id`) REFERENCES `section_1` (`id`)
    ON DELETE CASCADE
);

INSERT INTO `section_1_i18n` (section_id, lang, text)
SELECT id, 'ja', text FROM `section_1`;
```

> **검토 포인트**: 섹션 22개를 개별 `_i18n` 테이블로 만드는 것보다, 통합 테이블 `section_translations(section_num, section_id, lang, text)` 1개로 합치는 것도 고려할 만합니다. 현재 섹션별로 테이블이 나뉜 이유 자체가 마이그레이션 시 재검토 대상.

### 7-5. 사용자 언어 설정

```sql
USE A_DB;

ALTER TABLE `user_profile_summary`
  ADD COLUMN `preferred_lang` VARCHAR(5) NOT NULL DEFAULT 'ja'
  COMMENT '사용자 선호 언어 (ja/kr/en)';
```

---

## 8. 단계적 도입 로드맵

| 단계 | 작업 | 예상 영향 |
|---|---|---|
| **1단계** | `master_items_i18n` 생성, 기존 데이터 'ja'로 이관 | 코드 영향 없음 (양쪽 유지) |
| **2단계** | 라우터·모델에서 JOIN 쿼리로 변경, `user_profile_summary.preferred_lang` 추가 | 백엔드 수정, 기존 동작 동일 |
| **3단계** | 한국어 번역 데이터 INSERT (수동/번역 API 일괄) | 데이터만 추가 |
| **4단계** | 프론트 i18n 라이브러리 도입 + UI 라벨 한국어화 | 프론트 전체 수정 |
| **5단계** | Gemini 시스템 프롬프트에 언어 지시 추가 | 프롬프트 수정만 |
| **6단계** | 사용자 설정 화면에 언어 선택 UI | 신규 기능 |
| **7단계** | `cert_questions`, `section_*`, `cert_elements`, `pjt_elements` 같은 패턴 적용 | 동일 패턴 반복 |
| **8단계** | `master_items.name` 등 기존 컬럼 DROP (안정화 후) | cleanup |

---

## 9. 체크리스트 (도입 시 주의)

- [ ] FK `ON DELETE CASCADE` 설정 (마스터 삭제 시 번역도 같이 삭제)
- [ ] `COLLATE utf8mb4_unicode_ci` 명시 (한국어/이모지 안전)
- [ ] 폴백 언어 정책 명문화 (kr 없으면 ja, ja도 없으면 영어 등)
- [ ] 라우터에서 `session_id` → `preferred_lang` 조회 캐싱 (매 요청 JOIN 비용)
- [ ] Gemini 응답 언어가 시스템 프롬프트와 일치하는지 모니터링
- [ ] 텍스트 선택지(`option_1~4`)와 이미지 선택지 처리 분기 유지 (이미지 경로는 언어 무관)
- [ ] 백업: 기존 `master_items.name` 등 컬럼은 한동안 유지 후 안정화 확인 뒤 DROP