Files
2026-06-29 19:27:33 +08:00

202 lines
5.4 KiB
Markdown

# E-R 图
## 1. 说明
以下 E-R 图以当前 `mlkj-website/backend/prisma/schema.prisma` 为基础,标注现有实体并补充管理端需要新增的内容版本、文件绑定和作品评选实体。
图中的 `CONTENT_PAGE`、`CONTENT_SECTION`、`CONTENT_REVISION`、`FILE_BINDING`、`JUDGE_PROFILE`、`EVALUATION_*` 为建议新增表;其他多数表在现有 Prisma schema 中已经存在。
## 2. 核心 E-R 图
```mermaid
erDiagram
ADMIN_USER ||--o{ ADMIN_USER_ROLE : has
ADMIN_ROLE ||--o{ ADMIN_USER_ROLE : grants
ADMIN_USER ||--o{ OPERATION_LOG : writes
ADMIN_USER ||--o{ FILE : uploads
ADMIN_USER ||--o| JUDGE_PROFILE : may_be
CONTENT_PAGE ||--o{ CONTENT_SECTION : contains
CONTENT_SECTION ||--o{ CONTENT_REVISION : versions
ADMIN_USER ||--o{ CONTENT_REVISION : edits
SITE_CONFIG ||--o{ CONTENT_REVISION : versioned_by
HOME_SECTION ||--o{ CONTENT_REVISION : versioned_by
COMPETITION ||--o{ COMPETITION_SCHEDULE : has
COMPETITION ||--o{ COMPETITION_RESOURCE : has
COMPETITION ||--o{ PARTNER : has
COMPETITION ||--o{ COURSE : has
COMPETITION ||--o{ WORK : has
COMPETITION ||--o{ CONTENT_SECTION : maps_to
FILE ||--o{ FILE_BINDING : binds
FILE ||--o{ COMPETITION_RESOURCE : used_by
FILE ||--o{ WORK_IMAGE : image_of
FILE ||--o{ WORK_FILE : file_of
FILE ||--o{ COURSE : media_of
WORK ||--o{ WORK_IMAGE : has
WORK ||--o{ WORK_FILE : has
WORK ||--o{ WORK_VOTE : receives
WORK ||--o{ EVALUATION_ASSIGNMENT : assigned
WORK ||--o{ EVALUATION_RESULT : results
EVALUATION_ROUND ||--o{ EVALUATION_CRITERION : defines
EVALUATION_ROUND ||--o{ EVALUATION_ASSIGNMENT : includes
EVALUATION_ROUND ||--o{ EVALUATION_RESULT : produces
JUDGE_PROFILE ||--o{ EVALUATION_ASSIGNMENT : receives
EVALUATION_ASSIGNMENT ||--o{ WORK_SCORE : has
EVALUATION_CRITERION ||--o{ WORK_SCORE : scores
```
## 3. 内容发布关系
```mermaid
erDiagram
CONTENT_PAGE ||--o{ CONTENT_SECTION : contains
CONTENT_SECTION ||--o{ CONTENT_REVISION : has
ADMIN_USER ||--o{ CONTENT_REVISION : edited_by
CONTENT_PAGE {
bigint id PK
string page_key UK
string page_name
string page_type
string lang
string status
}
CONTENT_SECTION {
bigint id PK
bigint page_id FK
string section_key
string title
string status
int sort_order
}
CONTENT_REVISION {
bigint id PK
bigint section_id FK
string revision_status
json content_snapshot
bigint created_by FK
datetime published_at
}
```
## 4. 文件与业务绑定关系
```mermaid
erDiagram
FILE ||--o{ FILE_BINDING : has
FILE_BINDING }o--|| ADMIN_USER : created_by
FILE {
bigint id PK
string file_key UK
string original_name
string url
string mime_type
bigint size
string scene
string status
}
FILE_BINDING {
bigint id PK
bigint file_id FK
string target_type
bigint target_id
string usage
int sort_order
}
```
`target_type` 示例:
| target_type | target_id 指向 |
| --- | --- |
| `home_section` | `home_sections.id` |
| `competition` | `competitions.id` |
| `competition_resource` | `competition_resources.id` |
| `course` | `courses.id` |
| `work` | `works.id` |
| `work_image` | `work_images.id` |
| `work_file` | `work_files.id` |
## 5. 作品评选关系
```mermaid
erDiagram
ADMIN_USER ||--o| JUDGE_PROFILE : owns
EVALUATION_ROUND ||--o{ EVALUATION_CRITERION : has
EVALUATION_ROUND ||--o{ EVALUATION_ASSIGNMENT : has
JUDGE_PROFILE ||--o{ EVALUATION_ASSIGNMENT : assigned
WORK ||--o{ EVALUATION_ASSIGNMENT : assigned
EVALUATION_ASSIGNMENT ||--o{ WORK_SCORE : scores
EVALUATION_CRITERION ||--o{ WORK_SCORE : criterion
EVALUATION_ROUND ||--o{ EVALUATION_RESULT : produces
WORK ||--o{ EVALUATION_RESULT : ranked
EVALUATION_ROUND {
bigint id PK
bigint competition_id FK
string name
string round_type
string status
decimal expert_weight
decimal public_vote_weight
}
EVALUATION_CRITERION {
bigint id PK
bigint round_id FK
string name
decimal max_score
decimal weight
}
EVALUATION_ASSIGNMENT {
bigint id PK
bigint round_id FK
bigint work_id FK
bigint judge_id FK
string status
}
WORK_SCORE {
bigint id PK
bigint assignment_id FK
bigint criterion_id FK
decimal score
string comment
}
EVALUATION_RESULT {
bigint id PK
bigint round_id FK
bigint work_id FK
decimal expert_score
decimal public_vote_score
decimal final_score
int rank
string award_level
}
```
## 6. 当前表与新增表清单
| 类型 | 表 | 状态 |
| --- | --- | --- |
| 站点配置 | `site_configs`、`home_sections` | 已存在,建议扩展版本能力 |
| 通用内容 | `content_pages`、`content_sections`、`content_revisions` | 建议新增 |
| 大赛 | `competitions`、`competition_schedules`、`competition_resources`、`partners` | 已存在,建议扩展字段 |
| 作品 | `works`、`work_images`、`work_files`、`work_votes` | 已存在,建议扩展评审状态 |
| 课程 | `courses` | 已存在,建议扩展文件关联 |
| 文件 | `files` | 已存在,建议扩展上传人、场景、状态 |
| 文件绑定 | `file_bindings` | 建议新增 |
| 评选 | `judge_profiles`、`evaluation_rounds`、`evaluation_criteria`、`evaluation_assignments`、`work_scores`、`evaluation_results` | 建议新增 |
| 权限日志 | `admin_users`、`admin_roles`、`admin_user_roles`、`operation_logs` | 已存在,建议接入真实鉴权 |