scripts/DB/final E-R Diagram.md

🎯 최종 E-R 다이어그램 (v2)

📊 최종 테이블 구성 (총 12개)

번호	테이블명	주요 속성	설명
1	users	user_id, email, name, role, api_key, password_hash	사용자 계정
2	document_types	doc_type_id, type_name, model_name, sorting_method	문서 타입 정의 (수정)
3	projects	project_id, user_id, doc_type_id, project_name, total_pages, status	프로젝트/세션
4	pages	page_id, project_id, page_number, image_path, analysis_status	페이지 정보
5	layout_elements	element_id, page_id, class_name, bbox_x/y/width/height	레이아웃 요소 (수정)
6	text_contents	text_id, element_id, ocr_text, ocr_confidence	OCR 결과
7	ai_descriptions	ai_desc_id, element_id, description, ai_model	AI 설명
8	question_groups	question_group_id, page_id, anchor_element_id, start_y, end_y	문제 그룹 (수정)
9	question_elements	qe_id, question_group_id, element_id, order_in_question	문제-요소 매핑
10	text_versions	version_id, page_id, user_id, content, version_number, version_type	텍스트 버전 관리
11	formatting_rules	rule_id, doc_type_id, class_name, prefix, suffix, indent_level	포맷팅 규칙
12	combined_results	combined_id, project_id, combined_text, combined_stats	통합 문서 캐시

🔷 최종 E-R 다이어그램 (시각화)

                                    ┌─────────────────────────────────────┐
                                    │           users                     │
                                    │─────────────────────────────────────│
                                    │ PK: user_id                         │
                                    │     email (UNIQUE)                  │
                                    │     name                            │
                                    │     role (admin/teacher/student)    │
                                    │     api_key (암호화)                │
                                    │     password_hash                   │
                                    │     created_at                      │
                                    │     updated_at                      │
                                    └─────────────────────────────────────┘
                                                │
                                                │ 1:N (한 사용자는 여러 프로젝트 생성)
                                                ↓
                    ┌───────────────────────────────────────────────────────────────┐
                    │                    document_types (수정)                      │
                    │───────────────────────────────────────────────────────────────│
                    │ PK: doc_type_id                                               │
                    │     type_name (worksheet/document/form) UNIQUE                │
                    │     model_name (SmartEyeSsen/DocLayout-YOLO)                  │
(수정)              │     sorting_method (question_based/reading_order)             │
                    │     description                                               │
                    │     created_at, updated_at                                    │
                    └───────────────────────────────────────────────────────────────┘
                                                │
                                                │ 1:N (한 타입으로 여러 프로젝트)
                                                ↓
                    ┌───────────────────────────────────────────────────────────────┐
                    │                      projects                                 │
                    │───────────────────────────────────────────────────────────────│
                    │ PK: project_id                                                │
                    │ FK: user_id → users(user_id)                                  │
                    │ FK: doc_type_id → document_types(doc_type_id)                 │
                    │     project_name                                              │
                    │     total_pages (자동 계산)                                   │
                    │     analysis_mode (auto/manual/hybrid)                        │
                    │     status (created/in_progress/completed/error)              │
                    │     created_at, updated_at                                    │
                    └───────────────────────────────────────────────────────────────┘
                                    │                           │
                                    │ 1:N                       │ 1:1
                                    │                           │
                                    ↓                           ↓
        ┌───────────────────────────────────────┐   ┌─────────────────────────────────┐
        │              pages                    │   │      combined_results           │
        │───────────────────────────────────────│   │─────────────────────────────────│
        │ PK: page_id                           │   │ PK: combined_id                 │
        │ FK: project_id → projects(project_id) │   │ FK: project_id (UNIQUE)         │
        │     page_number                       │   │     combined_text (LONGTEXT)    │
        │     image_path                        │   │     combined_stats (JSON)       │
        │     image_width, image_height         │   │     generated_at                │
        │     analysis_status (pending/         │   │     updated_at                  │
        │         processing/completed/error)   │   └─────────────────────────────────┘
        │     processing_time                   │
        │     created_at, analyzed_at           │
        └───────────────────────────────────────┘
                    │                       │
                    │ 1:N                   │ 1:N
                    │                       │
                    ↓                       ↓
    ┌────────────────────────────────┐  ┌──────────────────────────────────────┐
    │    layout_elements (수정)        │  │        text_versions                 │
    │────────────────────────────────│  │──────────────────────────────────────│
    │ PK: element_id                 │  │ PK: version_id                       │
    │ FK: page_id → pages(page_id)   │  │ FK: page_id → pages(page_id)         │
    │     class_name                 │  │ FK: user_id → users(user_id)         │
    │     confidence                 │  │     content (TEXT)                   │
    │     bbox_x, bbox_y             │  │     version_number                   │
    │     bbox_width, bbox_height    │  │     version_type (original/          │
    │     area (자동 계산)            │  │         auto_formatted/user_edited)  │
    │     y_position (자동 계산)      │  │     is_current (TRUE/FALSE)          │
    │     x_position (자동 계산)      │  │     created_at                       │
(삭제)│     (order_index)              │  └──────────────────────────────────────┘
    │     created_at                 │
    └────────────────────────────────┘
                │
                ├──────────────────────┬──────────────────────┬──────────────────────┐
                │ 1:1 (OCR)            │ 1:1 (AI)             │ 1:N (자식 요소)      │ 1:1 (앵커)
                │                      │                      │                      │
                ↓                      ↓                      ↓                      ↓ (관계 추가)
    ┌─────────────────────┐  ┌─────────────────────┐  ┌──────────────────────────┐ ┌────────────────────────┐
    │   text_contents     │  │  ai_descriptions    │  │   question_elements      │ │ question_groups (수정) │
    │─────────────────────│  │─────────────────────│  │──────────────────────────│ │────────────────────────│
    │ PK: text_id         │  │ PK: ai_desc_id      │  │ PK: qe_id                │ │ PK: question_group_id  │
    │ FK: element_id      │  │ FK: element_id      │  │ FK: question_group_id    │ │ FK: page_id            │
    │     (UNIQUE)        │  │     (UNIQUE)        │  │ FK: element_id           │ │ FK: anchor_element_id  │ (수정)
    │     ocr_text (TEXT) │  │     description     │  │     order_in_question    │ │     (UNIQUE)           │
    │     ocr_engine      │  │     ai_model        │  │     created_at           │ │     start_y, end_y     │
    │     ocr_confidence  │  │     prompt_used     │  └──────────────────────────┘ │     element_count      │
    │     language        │  │     created_at      │                │              │     created_at         │
    │     created_at      │  └─────────────────────┘                │ N:1          │ (삭제) (question_number) │
    └─────────────────────┘                                         │              └────────────────────────┘
                                                                    │                           ↑
                                                                    └───────────────────────────┘
                                                                        1:N (그룹은 여러 자식 요소를 가짐)

                        ┌─────────────────────────────────────────────────────┐
                        │              formatting_rules                       │
                        │─────────────────────────────────────────────────────│
                        │ PK: rule_id                                         │
                        │ FK: doc_type_id → document_types(doc_type_id)       │
                        │     class_name                                      │
                        │     prefix, suffix                                  │
                        │     indent_level                                    │
                        │     font_size, font_weight                          │
                        │     created_at, updated_at                          │
                        └─────────────────────────────────────────────────────┘

🔗 관계(Relationships) 상세 설명 (수정)

1️⃣ users ↔ projects (1:N)

- 한 사용자는 여러 프로젝트 생성 가능
- 한 프로젝트는 한 명의 사용자에게만 속함
- FK: projects.user_id → users.user_id
- ON DELETE CASCADE: 사용자 삭제 시 프로젝트도 삭제

2️⃣ document_types ↔ projects (1:N)

- 한 문서 타입(worksheet/document)으로 여러 프로젝트 생성 가능
- 한 프로젝트는 하나의 문서 타입만 가짐
- FK: projects.doc_type_id → document_types.doc_type_id
- ON DELETE RESTRICT: 타입 사용 중이면 삭제 불가

3️⃣ projects ↔ pages (1:N)

- 한 프로젝트는 여러 페이지 포함
- 한 페이지는 하나의 프로젝트에만 속함
- FK: pages.project_id → projects.project_id
- ON DELETE CASCADE: 프로젝트 삭제 시 페이지도 삭제
- 트리거: 페이지 추가 시 projects.total_pages 자동 증가

4️⃣ projects ↔ combined_results (1:1)

- 한 프로젝트는 하나의 통합 결과 캐시만 가짐
- 한 통합 결과는 하나의 프로젝트에만 속함
- FK: combined_results.project_id → projects.project_id (UNIQUE)
- ON DELETE CASCADE: 프로젝트 삭제 시 캐시도 삭제

5️⃣ pages ↔ layout_elements (1:N)

- 한 페이지는 여러 레이아웃 요소 포함
- 한 요소는 하나의 페이지에만 속함
- FK: layout_elements.page_id → pages.page_id
- ON DELETE CASCADE: 페이지 삭제 시 요소도 삭제

6️⃣ pages ↔ text_versions (1:N)

- 한 페이지는 여러 텍스트 버전 가짐 (1, 2, 3, ...)
- 한 버전은 하나의 페이지에만 속함
- FK: text_versions.page_id → pages.page_id
- ON DELETE CASCADE: 페이지 삭제 시 모든 버전 삭제
- UNIQUE KEY: (page_id, version_number) - 페이지 내 버전 번호 중복 방지

7️⃣ layout_elements ↔ text_contents (1:1)

- 한 레이아웃 요소는 하나의 OCR 결과만 가짐
- 한 OCR 결과는 하나의 요소에만 속함
- FK: text_contents.element_id → layout_elements.element_id (UNIQUE)
- ON DELETE CASCADE: 요소 삭제 시 OCR 결과도 삭제

8️⃣ layout_elements ↔ ai_descriptions (1:1)

- 한 레이아웃 요소(figure/table/flowchart)는 하나의 AI 설명만 가짐
- 한 AI 설명은 하나의 요소에만 속함
- FK: ai_descriptions.element_id → layout_elements.element_id (UNIQUE)
- ON DELETE CASCADE: 요소 삭제 시 AI 설명도 삭제

9️⃣ pages ↔ question_groups (1:N)

- 한 페이지는 여러 문제 그룹 포함
- 한 문제 그룹은 하나의 페이지에만 속함
- FK: question_groups.page_id → pages.page_id
- ON DELETE CASCADE: 페이지 삭제 시 문제 그룹도 삭제

🔟 (신규) layout_elements ↔ question_groups (1:1)

- '앵커' 요소와 '문제 그룹' 간의 관계
- 한 레이아웃 요소(앵커)는 하나의 문제 그룹만 생성 가능
- 한 문제 그룹은 하나의 앵커 요소에 의해 생성됨
- FK: question_groups.anchor_element_id → layout_elements.element_id
- ON DELETE CASCADE: 앵커 요소 삭제 시, 그룹 및 하위 요소 매핑(question_elements)도 연쇄 삭제

1️⃣1️⃣ question_groups ↔ question_elements (1:N)

- '문제 그룹'과 '자식 요소' 매핑 간의 관계
- 한 문제 그룹은 여러 자식 요소(텍스트, 그림, 선택지 등)를 포함
- 한 매핑 레코드는 하나의 문제 그룹에만 속함
- FK: question_elements.question_group_id → question_groups.question_group_id
- ON DELETE CASCADE: 문제 그룹 삭제 시 매핑도 삭제

1️⃣2️⃣ layout_elements ↔ question_elements (N:1)

- '자식 요소'와 '매핑' 간의 관계
- 한 레이아웃 요소(자식)는 하나의 문제 그룹에만 속함 (N:1)
- (한 문제는 여러 자식 요소를 가짐)
- FK: question_elements.element_id → layout_elements.element_id
- ON DELETE CASCADE: 자식 요소 삭제 시 매핑도 삭제
- UNIQUE KEY: (question_group_id, element_id) - 중복 매핑 방지

1️⃣3️⃣ document_types ↔ formatting_rules (1:N)

- 한 문서 타입은 여러 포맷팅 규칙 가짐
- 한 규칙은 하나의 문서 타입에만 속함
- FK: formatting_rules.doc_type_id → document_types.doc_type_id
- ON DELETE CASCADE: 타입 삭제 시 규칙도 삭제
- UNIQUE KEY: (doc_type_id, class_name) - 타입별 클래스 규칙 중복 방지