# 一、数据库 ER 关系说明

> 全球高校留学基础数据库 · Global Higher Education Study-Abroad Database
> 版本 v1.0 ｜ 库名 `llm_tzsinen_cn` ｜ MySQL 8.0 / InnoDB / utf8mb4

**TL;DR** —— 本库采用「**一主多卫星 + 关联表解耦 + 治理表横切**」三层结构：
① 以 `universities`（school_id）为**唯一主键锚点**，所有业务表单向挂载；
② 用 `collaborations` 解耦 `universities × partners` 的**多对多**；
③ 用 `data_sources` / `update_logs` / `data_conflicts` 三张**治理表横切全库**，实现可追溯、可更新、可校验。

---

## 1. 总体分层

```
┌──────────────────────────────────────────────────────────────┐
│  L0 治理层（横切）                                              │
│  data_sources（来源）  update_logs（变更留痕）                   │
│  data_conflicts（冲突裁决）                                     │
│       ↑ 所有业务表的 source_id 均指向 data_sources               │
└──────────────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────────────┐
│  L1 字典层（主数据）                                            │
│  countries → cities → universities                            │
│  subjects（学科）  ranking_providers（排名机构）                 │
│  partners（合作机构）  score_metrics（主观指标定义）              │
└──────────────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────────────┐
│  L2 业务层（围绕 universities 的卫星表）                         │
│  departments / programs / rankings / admissions /             │
│  costs_scholarships / china_student_metrics / research /      │
│  careers_alumni / campus_life_location / scores /             │
│  accreditations / institution_profile                         │
└──────────────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────────────┐
│  L3 应用层（网站功能支撑）                                       │
│  admin_users（后台账号） favorites（对比篮） search_logs（热度）  │
│  visa_immigration（按国家维护，被院校间接引用）                   │
└──────────────────────────────────────────────────────────────┘
```

---

## 2. 实体关系全图（Mermaid）

```mermaid
erDiagram
    countries ||--o{ cities        : "1:N 拥有城市"
    countries ||--o{ universities  : "1:N 所在地"
    countries ||--o{ visa_immigration : "1:N 签证政策"
    cities    ||--o{ universities  : "1:N 坐落于"

    universities ||--o{ departments : "1:N 下设院系"
    departments  ||--o{ departments : "1:N 自关联(院系树)"
    universities ||--o{ programs    : "1:N 开设项目"
    departments  ||--o{ programs    : "1:N 归属项目"

    universities ||--o{ rankings    : "1:N 历年排名"
    ranking_providers ||--o{ rankings : "1:N 排名机构"
    subjects     ||--o{ rankings    : "1:N 学科排名"
    subjects     ||--o{ programs    : "1:N 专业分类"

    universities ||--o{ admissions  : "1:N 录取要求"
    programs     ||--o{ admissions  : "1:N 项目级要求"
    universities ||--o{ costs_scholarships : "1:N 费用奖学金"
    programs     ||--o{ costs_scholarships : "1:N 项目级费用"

    universities ||--o{ china_student_metrics : "1:N 中国学生指标(按学年)"
    universities ||--o{ research     : "1:N 科研(按年)"
    universities ||--o{ careers_alumni : "1:N 就业校友(按年)"
    universities ||--o{ accreditations : "1:N 认证记录"
    universities ||--o{ institution_profile : "1:N 财务与战略(按财年)"
    universities ||--|| campus_life_location : "1:1 生活地理"

    universities ||--o{ scores       : "1:N 主观评分"
    score_metrics ||--o{ scores      : "1:N 指标定义"

    universities ||--o{ collaborations : "1:N 合作项目"
    partners     ||--o{ collaborations : "1:N 合作方"
    universities }o--o{ partners       : "M:N 通过collaborations"
    departments  ||--o{ collaborations : "1:N 对接院系"

    data_sources ||--o{ universities  : "1:N 来源追溯"
    data_sources ||--o{ scores        : "1:N 来源追溯"
    data_sources ||--o{ rankings      : "1:N 来源追溯"
    data_conflicts }o--|| data_sources : "N:1 冲突双来源"
```

---

## 3. 关系明细表

### 3.1 一对多（1:N）—— 主体关系

| 父表（1） | 子表（N） | 外键 | 删除策略 | 业务含义 |
|---|---|---|---|---|
| `countries` | `cities` | cities.country_code | RESTRICT | 一个国家多个城市 |
| `countries` | `universities` | universities.country_code | RESTRICT | 一个国家多所高校 |
| `countries` | `visa_immigration` | visa_immigration.country_code | CASCADE | 一国多条签证/移民政策（学生签、毕业生签、工签） |
| `cities` | `universities` | universities.city_id | RESTRICT | 一个城市多所高校 |
| `cities` | `campus_life_location` | campus_life_location.city_id | SET NULL | 城市生活指标被引用 |
| `universities` | `departments` | departments.school_id | CASCADE | 一校多个学院/学系 |
| `departments` | `departments` | parent_dept_id（自关联，无物理外键） | — | 学部 → 学院 → 系 三级树 |
| `universities` | `programs` | programs.school_id | CASCADE | 一校多个项目 |
| `departments` | `programs` | programs.dept_id | SET NULL | 项目归属院系 |
| `subjects` | `programs` | programs.subject_code | SET NULL | 学科分类 |
| `universities` | `rankings` | rankings.school_id | CASCADE | 一校多条排名（多机构×多年×多学科） |
| `ranking_providers` | `rankings` | rankings.provider_code | RESTRICT | 一个机构发布多年榜单 |
| `subjects` | `rankings` | rankings.subject_code | SET NULL | 学科排名（NULL=综合排名） |
| `universities` | `admissions` | admissions.school_id | CASCADE | 一校多套录取要求（分层次/分项目/分学年） |
| `programs` | `admissions` | admissions.program_id | CASCADE | 项目级录取要求 |
| `universities` | `costs_scholarships` | costs_scholarships.school_id | CASCADE | 一校多年费用 |
| `programs` | `costs_scholarships` | costs_scholarships.program_id | CASCADE | 项目级费用 |
| `universities` | `china_student_metrics` | china_student_metrics.school_id | CASCADE | **每校每学年一条** |
| `universities` | `research` | research.school_id | CASCADE | **每校每年一条** |
| `universities` | `careers_alumni` | careers_alumni.school_id | CASCADE | **每校每年一条** |
| `universities` | `accreditations` | accreditations.school_id | CASCADE | 一校多条认证（中留服/教育部/专业认证） |
| `universities` | `institution_profile` | institution_profile.school_id | CASCADE | **每校每财年一条** |
| `universities` | `scores` | scores.school_id | CASCADE | 一校多条主观评分（多指标×多基准日） |
| `score_metrics` | `scores` | scores.metric_code | RESTRICT | 一个指标定义对应多校打分 |
| `universities` | `collaborations` | collaborations.school_id | CASCADE | 一校多个合作项目 |
| `partners` | `collaborations` | collaborations.partner_id | SET NULL | 一个机构合作多校 |
| `departments` | `collaborations` | collaborations.dept_id | SET NULL | 院级合作 |
| `data_sources` | 各业务表 | 各表 source_id | SET NULL | 来源删除后保留数据但失去追溯（由质量规则阻断） |
| `universities` | `favorites` | favorites.school_id | CASCADE | 用户收藏 |

### 3.2 一对一（1:1）

| 关系 | 实现方式 | 说明 |
|---|---|---|
| `universities` 1:1 `campus_life_location` | `UNIQUE KEY (school_id)` | 每校仅一条生活地理记录，物理上仍是 N:1，靠唯一约束实现 1:1 |

### 3.3 多对多（M:N）

| 关系 | 关联表 | 关联表附加属性 |
|---|---|---|
| `universities` M:N `partners` | **`collaborations`** | collab_type（2+2/3+1/双学位/交换…）、start_year、end_year、cn_students_per_year、credit_transferable、degree_awarded、funding_source —— **关联表自带业务属性**，是典型"带属性的多对多" |
| `universities` M:N `subjects` | **`rankings`** | 通过 (school_id, provider_code, rank_year, subject_code, region_scope) 唯一约束，实现"学校×学科×机构×年份"的四维多对多 |

### 3.4 多态关联（无物理外键，靠约定）

| 表 | 关联方式 | 原因 |
|---|---|---|
| `update_logs` | `table_name` + `record_id`（字符串化主键） | 需对**任意表**做字段级留痕，物理外键无法表达；且日志表要求高写入性能 |
| `data_conflicts` | `table_name` + `record_id` + `field_name`；`source_a_id` / `source_b_id` → `data_sources` | 冲突可在任意表任意字段发生 |
| `search_logs` | 无外键 | 高频行为日志，避免写入放大 |

---

## 4. 主键与唯一性设计

| 表 | 主键 | 设计理由 |
|---|---|---|
| `universities` | `school_id` **CHAR(16)**，格式 `国家码-城市码-序号`（如 `GB-LON-0001`） | 🟢 业务主键可读、可跨系统引用、便于 URL 与 CSV 交换；避免使用自增 ID 导致跨环境导入错位 |
| 业务卫星表 | 自增 `INT UNSIGNED` | 纯技术主键，配合 `(school_id, 时间维度)` 唯一约束防重复 |
| `data_sources` | 自增 `source_id` | 全库引用锚点 |
| `rankings` | 自增，UNIQUE `(school_id, provider_code, rank_year, subject_code, region_scope)` | 防同一学校同榜同年重复录入 |
| `china_student_metrics` / `research` / `careers_alumni` | 自增，UNIQUE `(school_id, 学年/年份)` | 保证**每校每期一条**，天然支持历史版本 |
| `scores` | 自增，UNIQUE `(school_id, metric_code, as_of)` | 同一指标不同基准日保留多次评分，支持**评分变化追踪** |
| `admissions` | 自增，UNIQUE `(school_id, program_id, degree_level, academic_year)` | 支持"校级通用要求"与"项目级要求"并存 |
| `costs_scholarships` | 自增，UNIQUE `(school_id, program_id, academic_year)` | 同上 |

---

## 5. 冗余字段设计（性能取舍）

为支撑筛选/排序/列表页的高性能，在 `universities` 主表冗余了以下"速查"字段，并约定**由定时任务从明细表回写**，明细表为唯一事实源：

| 冗余字段 | 来源 | 回写频率 |
|---|---|---|
| `qs_rank` / `the_rank` / `usnews_rank` / `arwu_rank` | `rankings`（取最新年份） | 排名发布后 |
| `annual_tuition_min/max`、`total_budget_est` | `costs_scholarships`（最新学年） | 每年 |
| `acceptance_rate` | `admissions`（校级最新学年） | 每年 |
| `employment_rate` | `careers_alumni`（最新年份） | 每年 |
| `cn_student_count` / `cn_student_pct` | `china_student_metrics`（最新学年） | 每年 |
| `preference_score` | `scores`（metric=CHINA_PREFERENCE 最新已核实） | 每年 |
| `moe_verified` / `cscse_recognized` / `accreditation_status` | `accreditations` | 每季度 |

🟡 **风险与对冲**：冗余可能不一致 → 由质量校验脚本 `sql/99_quality_checks.sql` 的「跨表一致性」规则定时比对，偏差超阈值即告警。

---

## 6. 时间维度设计

本库是**带时间切片**的数据库，三类时间字段必须区分清楚：

| 字段 | 含义 | 用途 |
|---|---|---|
| `as_of` | **数据基准日**（官方口径日期） | 时效校验、评分基准、前端"数据截至"展示 |
| `collected_at`（在 data_sources） | **采集时间** | 采集链路监控 |
| `created_at` / `updated_at` | **记录时间** | 变更追踪 |

时间切片表（每期一条，靠 UNIQUE(school_id, 时间) 保证）：
`china_student_metrics`(学年) · `research`(年) · `careers_alumni`(年) · `institution_profile`(财年) · `rankings`(年) · `scores`(as_of) · `admissions`(学年) · `costs_scholarships`(学年)

---

## 7. 完整表清单（26 张）

| # | 表名 | 中文名 | 层级 | 字段数 |
|---|---|---|---|---|
| 1 | `data_sources` | 数据来源表 | L0 治理 | 20 |
| 2 | `update_logs` | 更新日志表 | L0 治理 | 16 |
| 3 | `data_conflicts` | 数据冲突表 | L0 治理 | 16 |
| 4 | `countries` | 国家与地区表 | L1 字典 | 17 |
| 5 | `cities` | 城市表 | L1 字典 | 23 |
| 6 | `subjects` | 学科分类表 | L1 字典 | 9 |
| 7 | `ranking_providers` | 排名机构表 | L1 字典 | 14 |
| 8 | `partners` | 合作机构表 | L1 字典 | 12 |
| 9 | `score_metrics` | 主观评分指标定义表 | L1 字典 | 10 |
| 10 | **`universities`** | **高校主表** | L2 业务 | 79 |
| 11 | **`departments`** | **院系设置表** | L2 业务 | 18 |
| 12 | **`programs`** | **专业/项目表** | L2 业务 | 37 |
| 13 | **`rankings`** | **排名表** | L2 业务 | 20 |
| 14 | **`admissions`** | **录取要求表** | L2 业务 | 55 |
| 15 | **`costs_scholarships`** | **费用与奖学金表** | L2 业务 | 44 |
| 16 | **`china_student_metrics`** | **中国学生指标表** | L2 业务 | 33 |
| 17 | **`collaborations`** | **对外合作表** | L2 业务 | 24 |
| 18 | **`research`** | **科研能力表** | L2 业务 | 26 |
| 19 | **`careers_alumni`** | **就业与校友表** | L2 业务 | 32 |
| 20 | **`campus_life_location`** | **生活与地理表** | L2 业务 | 40 |
| 21 | **`scores`** | **主观评分表** | L2 业务 | 24 |
| 22 | `accreditations` | 认证与学位认可度表 | L2 业务 | 19 |
| 23 | `institution_profile` | 院校发展与财务健康表 | L2 业务 | 25 |
| 24 | `visa_immigration` | 签证与移民政策表 | L2 业务 | 25 |
| 25 | `admin_users` | 后台账号表 | L3 应用 | 9 |
| 26 | `favorites` | 收藏与对比篮 | L3 应用 | 5 |
| 27 | `search_logs` | 搜索行为日志表 | L3 应用 | 8 |

> ⭐ 需求点名的 14 张表全部包含，另补 13 张（认证、签证移民、财务战略、冲突、账号、收藏、日志及 6 张字典表）以覆盖用户"必须补充"的盲区。

---

## 8. 需求覆盖对照

| 需求必须字段 | 承载表 | 字段 |
|---|---|---|
| 高校名称 / 英文名 | universities | `name_zh` / `name_en` |
| 国家 / 地区·城市 | countries + cities + universities | `country_code` / `city_id` |
| 院系设置 | departments | `name_zh` / `dept_type` / `parent_dept_id` |
| QS排名 | rankings（+ universities 冗余） | `provider_code='QS'` / `qs_rank` |
| 中国留学生喜好度 | scores（CHINA_PREFERENCE）+ universities | `preference_score` |
| 对外合作 | collaborations + partners | `collab_type` / `partner_name_zh` |
| 科研能力 | research + scores | `h_index` / `research_score` |
| 未来发展 | careers_alumni + scores | `employment_rate` / `future_outlook_score` |
| 校友情况 | careers_alumni + scores | `alumni_total` / `alumni_score` |