cykj/hzdyjs
1
0 Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
Files
main
hzdyjs/docs/需求文档/奖惩补贴需求/奖学金管理数据模型设计.md
T
wuzhen 9b7a6e2103 wuzhen update
2025-08-07 18:40:25 +08:00

581 lines
32 KiB
Markdown
Raw Permalink Blame History

# 奖学金管理模块数据模型设计
**文档版本**: v1.0
**创建时间**: 2025-01-27
**创建人**: AI Assistant
**最后更新**: 2025-01-27
---
## 📋 1. 设计概述
### 1.1 设计原则
- **标准化**: 遵循项目统一的数据库规范
- **灵活性**: 规则引擎支持复杂业务规则配置
- **扩展性**: 支持新增奖学金类型和评定规则
- **性能优化**: 合理设计索引，支持高并发查询
- **数据安全**: 逻辑删除，多租户隔离
### 1.2 技术规范
- **主键**: 统一使用雪花ID (`bigint(20)`)
- **基础字段**: 所有表包含统一的基础字段
- **逻辑删除**: 使用 `deleted` 字段，禁止物理删除
- **多租户**: 包含 `tenant_id` 字段
- **字符编码**: UTF8MB4，支持emoji等特殊字符
---
## 🗃️ 2. 核心表结构设计
### 2.1 奖学金类型管理
#### 2.1.1 奖学金类型表 (jcbt_scholarship_type)
```sql
CREATE TABLE jcbt_scholarship_type (
id BIGINT(20) NOT NULL COMMENT '主键ID',
type_code VARCHAR(50) NOT NULL COMMENT '奖学金类型编码',
type_name VARCHAR(100) NOT NULL COMMENT '奖学金名称',
type_category VARCHAR(20) NOT NULL COMMENT '奖学金类别：ACADEMIC-学业奖学金，INNOVATION-创新奖学金，SOCIAL-社会工作奖学金，SPECIAL-专项奖学金',
level_type VARCHAR(20) DEFAULT NULL COMMENT '等级类型：FIRST-一等，SECOND-二等，THIRD-三等，SPECIAL-特等',
award_amount DECIMAL(10,2) DEFAULT NULL COMMENT '奖励金额',
description TEXT DEFAULT NULL COMMENT '奖学金描述',
application_guide TEXT DEFAULT NULL COMMENT '申请指南',
status TINYINT(1) DEFAULT 1 COMMENT '状态：0-禁用，1-启用',
sort_order INT DEFAULT 0 COMMENT '排序号',
is_system TINYINT(1) DEFAULT 0 COMMENT '是否系统内置：0-否，1-是',
tenant_id BIGINT(20) NOT NULL COMMENT '租户ID',
create_by BIGINT(20) DEFAULT NULL COMMENT '创建者',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_by BIGINT(20) DEFAULT NULL COMMENT '更新者',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
deleted TINYINT(1) DEFAULT 0 COMMENT '删除标记：0-正常，1-删除',
PRIMARY KEY (id),
UNIQUE KEY uk_type_code_tenant (type_code, tenant_id, deleted),
INDEX idx_category_status (type_category, status),
INDEX idx_tenant_status (tenant_id, status, deleted),
INDEX idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='奖学金类型表';
```
#### 2.1.2 奖学金兼得关系表 (jcbt_scholarship_compatibility)
```sql
CREATE TABLE jcbt_scholarship_compatibility (
id BIGINT(20) NOT NULL COMMENT '主键ID',
type_id_1 BIGINT(20) NOT NULL COMMENT '奖学金类型1',
type_id_2 BIGINT(20) NOT NULL COMMENT '奖学金类型2',
relation_type VARCHAR(20) NOT NULL COMMENT '关系类型：COMPATIBLE-兼容，EXCLUSIVE-互斥，PRIORITY-优先级关系',
priority_level INT DEFAULT 0 COMMENT '优先级（relation_type为PRIORITY时有效）',
description VARCHAR(500) DEFAULT NULL COMMENT '关系描述',
tenant_id BIGINT(20) NOT NULL COMMENT '租户ID',
create_by BIGINT(20) DEFAULT NULL COMMENT '创建者',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_by BIGINT(20) DEFAULT NULL COMMENT '更新者',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
deleted TINYINT(1) DEFAULT 0 COMMENT '删除标记：0-正常，1-删除',
PRIMARY KEY (id),
UNIQUE KEY uk_types_tenant (type_id_1, type_id_2, tenant_id, deleted),
INDEX idx_type1_relation (type_id_1, relation_type),
INDEX idx_type2_relation (type_id_2, relation_type),
INDEX idx_tenant (tenant_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='奖学金兼得关系表';
```
### 2.2 灵活规则引擎设计
#### 2.2.1 评定规则主表 (jcbt_scholarship_rule)
```sql
CREATE TABLE jcbt_scholarship_rule (
id BIGINT(20) NOT NULL COMMENT '主键ID',
rule_code VARCHAR(50) NOT NULL COMMENT '规则编码',
rule_name VARCHAR(100) NOT NULL COMMENT '规则名称',
scholarship_type_id BIGINT(20) NOT NULL COMMENT '关联奖学金类型ID',
rule_type VARCHAR(20) NOT NULL COMMENT '规则类型：AUTO-自动评定，MANUAL-手动申请，HYBRID-混合模式',
evaluation_method VARCHAR(20) NOT NULL COMMENT '评定方法：SCORE-积分制，RANKING-排名制，THRESHOLD-阈值制',
min_score DECIMAL(8,2) DEFAULT NULL COMMENT '最低分数要求',
is_active TINYINT(1) DEFAULT 1 COMMENT '是否激活：0-否，1-是',
priority_level INT DEFAULT 0 COMMENT '规则优先级',
description TEXT DEFAULT NULL COMMENT '规则描述',
tenant_id BIGINT(20) NOT NULL COMMENT '租户ID',
create_by BIGINT(20) DEFAULT NULL COMMENT '创建者',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_by BIGINT(20) DEFAULT NULL COMMENT '更新者',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
deleted TINYINT(1) DEFAULT 0 COMMENT '删除标记：0-正常，1-删除',
PRIMARY KEY (id),
UNIQUE KEY uk_rule_code_tenant (rule_code, tenant_id, deleted),
INDEX idx_scholarship_type (scholarship_type_id),
INDEX idx_rule_type_active (rule_type, is_active),
INDEX idx_tenant_active (tenant_id, is_active, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='奖学金评定规则主表';
```
#### 2.2.2 规则条件表 (jcbt_scholarship_rule_condition)
```sql
CREATE TABLE jcbt_scholarship_rule_condition (
id BIGINT(20) NOT NULL COMMENT '主键ID',
rule_id BIGINT(20) NOT NULL COMMENT '规则ID',
condition_group VARCHAR(50) NOT NULL COMMENT '条件组标识（同组内为AND关系，不同组为OR关系）',
field_name VARCHAR(100) NOT NULL COMMENT '字段名称',
field_type VARCHAR(20) NOT NULL COMMENT '字段类型：NUMBER-数字，STRING-字符串，DATE-日期，BOOLEAN-布尔',
operator_type VARCHAR(20) NOT NULL COMMENT '操作符：EQ-等于，GT-大于，GTE-大于等于，LT-小于，LTE-小于等于，IN-包含，NOT_IN-不包含，LIKE-模糊匹配，BETWEEN-区间',
condition_value TEXT NOT NULL COMMENT '条件值（JSON格式存储复杂数据）',
weight DECIMAL(5,2) DEFAULT 1.00 COMMENT '权重',
is_required TINYINT(1) DEFAULT 1 COMMENT '是否必要条件：0-否，1-是',
error_message VARCHAR(200) DEFAULT NULL COMMENT '不满足条件时的提示信息',
sort_order INT DEFAULT 0 COMMENT '排序号',
tenant_id BIGINT(20) NOT NULL COMMENT '租户ID',
create_by BIGINT(20) DEFAULT NULL COMMENT '创建者',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_by BIGINT(20) DEFAULT NULL COMMENT '更新者',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
deleted TINYINT(1) DEFAULT 0 COMMENT '删除标记：0-正常，1-删除',
PRIMARY KEY (id),
INDEX idx_rule_group (rule_id, condition_group),
INDEX idx_field_operator (field_name, operator_type),
INDEX idx_tenant (tenant_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='规则条件表';
```
#### 2.2.3 规则动作表 (jcbt_scholarship_rule_action)
```sql
CREATE TABLE jcbt_scholarship_rule_action (
id BIGINT(20) NOT NULL COMMENT '主键ID',
rule_id BIGINT(20) NOT NULL COMMENT '规则ID',
action_type VARCHAR(20) NOT NULL COMMENT '动作类型：CALCULATE-计算分数，NOTIFY-通知，LOG-记录日志，EXECUTE-执行方法',
action_config TEXT NOT NULL COMMENT '动作配置（JSON格式）',
execution_order INT DEFAULT 0 COMMENT '执行顺序',
is_enabled TINYINT(1) DEFAULT 1 COMMENT '是否启用：0-否，1-是',
tenant_id BIGINT(20) NOT NULL COMMENT '租户ID',
create_by BIGINT(20) DEFAULT NULL COMMENT '创建者',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_by BIGINT(20) DEFAULT NULL COMMENT '更新者',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
deleted TINYINT(1) DEFAULT 0 COMMENT '删除标记：0-正常，1-删除',
PRIMARY KEY (id),
INDEX idx_rule_order (rule_id, execution_order),
INDEX idx_action_type (action_type),
INDEX idx_tenant (tenant_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='规则动作表';
```
### 2.3 批次与名额管理
#### 2.3.1 评定批次表 (jcbt_scholarship_batch)
```sql
CREATE TABLE jcbt_scholarship_batch (
id BIGINT(20) NOT NULL COMMENT '主键ID',
batch_code VARCHAR(50) NOT NULL COMMENT '批次编码',
batch_name VARCHAR(100) NOT NULL COMMENT '批次名称',
academic_year VARCHAR(20) NOT NULL COMMENT '学年',
semester TINYINT(1) NOT NULL COMMENT '学期：1-第一学期，2-第二学期，3-全年',
batch_type VARCHAR(20) NOT NULL COMMENT '批次类型：REGULAR-常规批次，SPECIAL-专项批次，SUPPLEMENTARY-补充批次',
start_time DATETIME NOT NULL COMMENT '开始时间',
end_time DATETIME NOT NULL COMMENT '结束时间',
apply_start_time DATETIME DEFAULT NULL COMMENT '申请开始时间',
apply_end_time DATETIME DEFAULT NULL COMMENT '申请结束时间',
review_start_time DATETIME DEFAULT NULL COMMENT '审核开始时间',
review_end_time DATETIME DEFAULT NULL COMMENT '审核结束时间',
status VARCHAR(20) DEFAULT 'DRAFT' COMMENT '状态：DRAFT-草稿，ACTIVE-进行中，COMPLETED-已完成，CANCELLED-已取消',
description TEXT DEFAULT NULL COMMENT '批次描述',
notice_content TEXT DEFAULT NULL COMMENT '通知内容',
tenant_id BIGINT(20) NOT NULL COMMENT '租户ID',
create_by BIGINT(20) DEFAULT NULL COMMENT '创建者',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_by BIGINT(20) DEFAULT NULL COMMENT '更新者',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
deleted TINYINT(1) DEFAULT 0 COMMENT '删除标记：0-正常，1-删除',
PRIMARY KEY (id),
UNIQUE KEY uk_batch_code_tenant (batch_code, tenant_id, deleted),
INDEX idx_academic_year (academic_year, semester),
INDEX idx_status_time (status, start_time, end_time),
INDEX idx_tenant_status (tenant_id, status, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='评定批次表';
```
#### 2.3.2 名额分配表 (jcbt_scholarship_quota)
```sql
CREATE TABLE jcbt_scholarship_quota (
id BIGINT(20) NOT NULL COMMENT '主键ID',
batch_id BIGINT(20) NOT NULL COMMENT '批次ID',
scholarship_type_id BIGINT(20) NOT NULL COMMENT '奖学金类型ID',
allocation_level VARCHAR(20) NOT NULL COMMENT '分配级别：UNIVERSITY-全校，COLLEGE-学院，MAJOR-专业，CLASS-班级',
target_dept_id BIGINT(20) DEFAULT NULL COMMENT '目标部门ID（学院/专业/班级）',
total_quota INT NOT NULL DEFAULT 0 COMMENT '总名额',
used_quota INT NOT NULL DEFAULT 0 COMMENT '已使用名额',
reserved_quota INT NOT NULL DEFAULT 0 COMMENT '预留名额',
allocation_rule TEXT DEFAULT NULL COMMENT '分配规则（JSON格式）',
allocation_basis VARCHAR(500) DEFAULT NULL COMMENT '分配依据说明',
is_adjustable TINYINT(1) DEFAULT 1 COMMENT '是否可调整：0-否，1-是',
tenant_id BIGINT(20) NOT NULL COMMENT '租户ID',
create_by BIGINT(20) DEFAULT NULL COMMENT '创建者',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_by BIGINT(20) DEFAULT NULL COMMENT '更新者',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
deleted TINYINT(1) DEFAULT 0 COMMENT '删除标记：0-正常，1-删除',
PRIMARY KEY (id),
UNIQUE KEY uk_batch_type_dept (batch_id, scholarship_type_id, target_dept_id, deleted),
INDEX idx_batch_level (batch_id, allocation_level),
INDEX idx_dept_quota (target_dept_id, total_quota),
INDEX idx_tenant (tenant_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='名额分配表';
```
### 2.4 申请与审核管理
#### 2.4.1 申请记录表 (jcbt_scholarship_application)
```sql
CREATE TABLE jcbt_scholarship_application (
id BIGINT(20) NOT NULL COMMENT '主键ID',
application_no VARCHAR(50) NOT NULL COMMENT '申请编号',
batch_id BIGINT(20) NOT NULL COMMENT '批次ID',
scholarship_type_id BIGINT(20) NOT NULL COMMENT '奖学金类型ID',
student_id BIGINT(20) NOT NULL COMMENT '学生ID',
student_no VARCHAR(50) NOT NULL COMMENT '学号',
student_name VARCHAR(100) NOT NULL COMMENT '学生姓名',
dept_id BIGINT(20) NOT NULL COMMENT '所属部门ID',
major_id BIGINT(20) DEFAULT NULL COMMENT '专业ID',
class_id BIGINT(20) DEFAULT NULL COMMENT '班级ID',
application_type VARCHAR(20) NOT NULL COMMENT '申请类型：STUDENT-学生申请，AUTO-自动评定，DIRECT-直接指定',
current_status VARCHAR(20) DEFAULT 'PENDING' COMMENT '当前状态：PENDING-待审核，APPROVED-已通过，REJECTED-已拒绝，WITHDRAWN-已撤回',
apply_reason TEXT DEFAULT NULL COMMENT '申请理由',
self_evaluation TEXT DEFAULT NULL COMMENT '自我评价',
calculated_score DECIMAL(8,2) DEFAULT NULL COMMENT '系统计算分数',
final_score DECIMAL(8,2) DEFAULT NULL COMMENT '最终评定分数',
ranking_in_dept INT DEFAULT NULL COMMENT '在院系的排名',
ranking_in_major INT DEFAULT NULL COMMENT '在专业的排名',
submit_time DATETIME DEFAULT NULL COMMENT '提交时间',
tenant_id BIGINT(20) NOT NULL COMMENT '租户ID',
create_by BIGINT(20) DEFAULT NULL COMMENT '创建者',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_by BIGINT(20) DEFAULT NULL COMMENT '更新者',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
deleted TINYINT(1) DEFAULT 0 COMMENT '删除标记：0-正常，1-删除',
PRIMARY KEY (id),
UNIQUE KEY uk_application_no (application_no),
INDEX idx_batch_type_student (batch_id, scholarship_type_id, student_id),
INDEX idx_student_status (student_id, current_status),
INDEX idx_dept_batch (dept_id, batch_id),
INDEX idx_status_score (current_status, final_score),
INDEX idx_tenant (tenant_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='奖学金申请记录表';
```
#### 2.4.2 申请材料表 (jcbt_scholarship_attachment)
```sql
CREATE TABLE jcbt_scholarship_attachment (
id BIGINT(20) NOT NULL COMMENT '主键ID',
application_id BIGINT(20) NOT NULL COMMENT '申请ID',
attachment_type VARCHAR(20) NOT NULL COMMENT '附件类型：TRANSCRIPT-成绩单，CERTIFICATE-证书，PROOF-证明材料，OTHER-其他',
file_name VARCHAR(200) NOT NULL COMMENT '文件名称',
file_path VARCHAR(500) NOT NULL COMMENT '文件路径',
file_size BIGINT DEFAULT NULL COMMENT '文件大小（字节）',
file_type VARCHAR(50) DEFAULT NULL COMMENT '文件类型',
upload_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '上传时间',
is_required TINYINT(1) DEFAULT 0 COMMENT '是否必需：0-否，1-是',
description VARCHAR(500) DEFAULT NULL COMMENT '附件描述',
tenant_id BIGINT(20) NOT NULL COMMENT '租户ID',
create_by BIGINT(20) DEFAULT NULL COMMENT '创建者',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_by BIGINT(20) DEFAULT NULL COMMENT '更新者',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
deleted TINYINT(1) DEFAULT 0 COMMENT '删除标记：0-正常，1-删除',
PRIMARY KEY (id),
INDEX idx_application_type (application_id, attachment_type),
INDEX idx_upload_time (upload_time),
INDEX idx_tenant (tenant_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='申请材料附件表';
```
#### 2.4.3 审核记录表 (jcbt_scholarship_review)
```sql
CREATE TABLE jcbt_scholarship_review (
id BIGINT(20) NOT NULL COMMENT '主键ID',
application_id BIGINT(20) NOT NULL COMMENT '申请ID',
review_level VARCHAR(20) NOT NULL COMMENT '审核级别：DEPT-院系审核，UNIVERSITY-校级审核',
reviewer_id BIGINT(20) NOT NULL COMMENT '审核人ID',
reviewer_name VARCHAR(100) NOT NULL COMMENT '审核人姓名',
review_result VARCHAR(20) NOT NULL COMMENT '审核结果：APPROVED-通过，REJECTED-拒绝，RETURNED-退回修改',
review_score DECIMAL(8,2) DEFAULT NULL COMMENT '审核评分',
review_opinion TEXT DEFAULT NULL COMMENT '审核意见',
review_time DATETIME NOT NULL COMMENT '审核时间',
next_reviewer_id BIGINT(20) DEFAULT NULL COMMENT '下一审核人ID',
is_final_review TINYINT(1) DEFAULT 0 COMMENT '是否最终审核：0-否，1-是',
tenant_id BIGINT(20) NOT NULL COMMENT '租户ID',
create_by BIGINT(20) DEFAULT NULL COMMENT '创建者',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_by BIGINT(20) DEFAULT NULL COMMENT '更新者',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
deleted TINYINT(1) DEFAULT 0 COMMENT '删除标记：0-正常，1-删除',
PRIMARY KEY (id),
INDEX idx_application_level (application_id, review_level),
INDEX idx_reviewer_time (reviewer_id, review_time),
INDEX idx_result_time (review_result, review_time),
INDEX idx_tenant (tenant_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='审核记录表';
```
### 2.5 获奖记录与统计
#### 2.5.1 获奖记录表 (jcbt_scholarship_award)
```sql
CREATE TABLE jcbt_scholarship_award (
id BIGINT(20) NOT NULL COMMENT '主键ID',
award_no VARCHAR(50) NOT NULL COMMENT '获奖编号',
application_id BIGINT(20) NOT NULL COMMENT '申请ID',
batch_id BIGINT(20) NOT NULL COMMENT '批次ID',
scholarship_type_id BIGINT(20) NOT NULL COMMENT '奖学金类型ID',
student_id BIGINT(20) NOT NULL COMMENT '学生ID',
student_no VARCHAR(50) NOT NULL COMMENT '学号',
student_name VARCHAR(100) NOT NULL COMMENT '学生姓名',
dept_id BIGINT(20) NOT NULL COMMENT '所属部门ID',
award_amount DECIMAL(10,2) NOT NULL COMMENT '奖励金额',
award_level VARCHAR(20) DEFAULT NULL COMMENT '获奖等级',
final_score DECIMAL(8,2) DEFAULT NULL COMMENT '最终得分',
ranking_in_dept INT DEFAULT NULL COMMENT '院系排名',
award_date DATE NOT NULL COMMENT '获奖日期',
certificate_no VARCHAR(100) DEFAULT NULL COMMENT '证书编号',
is_published TINYINT(1) DEFAULT 0 COMMENT '是否公示：0-否，1-是',
publish_date DATE DEFAULT NULL COMMENT '公示日期',
is_paid TINYINT(1) DEFAULT 0 COMMENT '是否已发放：0-否，1-是',
pay_date DATE DEFAULT NULL COMMENT '发放日期',
remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
tenant_id BIGINT(20) NOT NULL COMMENT '租户ID',
create_by BIGINT(20) DEFAULT NULL COMMENT '创建者',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_by BIGINT(20) DEFAULT NULL COMMENT '更新者',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
deleted TINYINT(1) DEFAULT 0 COMMENT '删除标记：0-正常，1-删除',
PRIMARY KEY (id),
UNIQUE KEY uk_award_no (award_no),
INDEX idx_batch_type (batch_id, scholarship_type_id),
INDEX idx_student_award (student_id, award_date),
INDEX idx_dept_batch (dept_id, batch_id),
INDEX idx_publish_pay (is_published, is_paid),
INDEX idx_tenant (tenant_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='奖学金获奖记录表';
```
---
## 📊 3. 字典表设计
### 3.1 系统字典表 (sys_dict_data)
```sql
-- 奖学金相关字典数据
INSERT INTO sys_dict_data (dict_type, dict_label, dict_value, dict_sort, status, remark) VALUES
-- 奖学金类别
('jcbt_scholarship_category', '学业奖学金', 'ACADEMIC', 1, '0', '基于学业成绩的奖学金'),
('jcbt_scholarship_category', '创新奖学金', 'INNOVATION', 2, '0', '基于创新能力的奖学金'),
('jcbt_scholarship_category', '社会工作奖学金', 'SOCIAL', 3, '0', '基于社会工作的奖学金'),
('jcbt_scholarship_category', '专项奖学金', 'SPECIAL', 4, '0', '特定条件的专项奖学金'),
-- 奖学金等级
('jcbt_scholarship_level', '特等奖学金', 'SPECIAL', 1, '0', '最高等级奖学金'),
('jcbt_scholarship_level', '一等奖学金', 'FIRST', 2, '0', '一等奖学金'),
('jcbt_scholarship_level', '二等奖学金', 'SECOND', 3, '0', '二等奖学金'),
('jcbt_scholarship_level', '三等奖学金', 'THIRD', 4, '0', '三等奖学金'),
-- 申请状态
('jcbt_application_status', '待审核', 'PENDING', 1, '0', '申请待审核状态'),
('jcbt_application_status', '已通过', 'APPROVED', 2, '0', '申请已通过'),
('jcbt_application_status', '已拒绝', 'REJECTED', 3, '0', '申请已拒绝'),
('jcbt_application_status', '已撤回', 'WITHDRAWN', 4, '0', '申请已撤回'),
-- 批次状态
('jcbt_batch_status', '草稿', 'DRAFT', 1, '0', '批次草稿状态'),
('jcbt_batch_status', '进行中', 'ACTIVE', 2, '0', '批次进行中'),
('jcbt_batch_status', '已完成', 'COMPLETED', 3, '0', '批次已完成'),
('jcbt_batch_status', '已取消', 'CANCELLED', 4, '0', '批次已取消');
```
---
## 🔧 4. 规则引擎设计详解
### 4.1 规则配置灵活性设计
#### 4.1.1 条件配置示例
```json
{
"field_name": "gpa",
"field_type": "NUMBER",
"operator_type": "GTE",
"condition_value": "3.5",
"weight": 0.4,
"error_message": "学业成绩绩点需≥3.5"
}
{
"field_name": "moral_score",
"field_type": "NUMBER",
"operator_type": "BETWEEN",
"condition_value": "[80, 100]",
"weight": 0.3,
"error_message": "德育分需在80-100分之间"
}
{
"field_name": "disciplinary_records",
"field_type": "BOOLEAN",
"operator_type": "EQ",
"condition_value": "false",
"weight": 0.1,
"error_message": "不能有违纪记录"
}
```
#### 4.1.2 复杂规则组合
```json
{
"rule_id": 1,
"conditions": [
{
"condition_group": "GROUP_A",
"field_name": "gpa",
"operator_type": "GTE",
"condition_value": "3.5"
},
{
"condition_group": "GROUP_A",
"field_name": "credit_completed",
"operator_type": "GTE",
"condition_value": "120"
},
{
"condition_group": "GROUP_B",
"field_name": "competition_awards",
"operator_type": "GTE",
"condition_value": "1"
}
]
}
```
**逻辑关系**: (GROUP_A的所有条件) OR (GROUP_B的所有条件)
### 4.2 支持的字段类型与操作符
| 字段类型 | 支持的操作符 | 示例值 |
|---------|-------------|--------|
| NUMBER | EQ, GT, GTE, LT, LTE, BETWEEN | "3.5", "[80, 90]" |
| STRING | EQ, LIKE, IN, NOT_IN | "优秀", "['优秀','良好']" |
| DATE | EQ, GT, GTE, LT, LTE, BETWEEN | "2024-01-01", "['2024-01-01','2024-12-31']" |
| BOOLEAN | EQ | "true", "false" |
---
## 📋 5. 索引优化方案
### 5.1 核心查询索引
```sql
-- 奖学金申请查询优化
CREATE INDEX idx_app_batch_status_score ON jcbt_scholarship_application
(batch_id, current_status, final_score DESC);
-- 获奖记录查询优化
CREATE INDEX idx_award_student_date ON jcbt_scholarship_award
(student_id, award_date DESC);
-- 规则条件查询优化
CREATE INDEX idx_rule_condition_field ON jcbt_scholarship_rule_condition
(rule_id, field_name, operator_type);
-- 名额统计查询优化
CREATE INDEX idx_quota_batch_dept_usage ON jcbt_scholarship_quota
(batch_id, target_dept_id, used_quota, total_quota);
```
### 5.2 分区表设计（可选）
```sql
-- 对于数据量大的申请记录表，可考虑按批次分区
ALTER TABLE jcbt_scholarship_application
PARTITION BY RANGE (batch_id) (
PARTITION p2024_1 VALUES LESS THAN (202401),
PARTITION p2024_2 VALUES LESS THAN (202402),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
```
---
## 🎯 6. 数据初始化脚本
### 6.1 奖学金类型基础数据
```sql
-- 插入基础奖学金类型
INSERT INTO jcbt_scholarship_type (id, type_code, type_name, type_category, level_type, award_amount, description, status, tenant_id, create_by, create_time) VALUES
(1, 'ACADEMIC_FIRST', '一等学业奖学金', 'ACADEMIC', 'FIRST', 5000.00, '综合成绩排名前5%的学生', 1, 1, 1, NOW()),
(2, 'ACADEMIC_SECOND', '二等学业奖学金', 'ACADEMIC', 'SECOND', 3000.00, '综合成绩排名前15%的学生', 1, 1, 1, NOW()),
(3, 'ACADEMIC_THIRD', '三等学业奖学金', 'ACADEMIC', 'THIRD', 1500.00, '综合成绩排名前30%的学生', 1, 1, 1, NOW()),
(4, 'INNOVATION_SPECIAL', '创新创业特等奖学金', 'INNOVATION', 'SPECIAL', 10000.00, '在创新创业方面有突出表现', 1, 1, 1, NOW());
-- 插入兼得关系配置
INSERT INTO jcbt_scholarship_compatibility (id, type_id_1, type_id_2, relation_type, priority_level, tenant_id, create_by, create_time) VALUES
(1, 1, 2, 'EXCLUSIVE', 0, 1, 1, NOW()),
(2, 1, 3, 'EXCLUSIVE', 0, 1, 1, NOW()),
(3, 2, 3, 'EXCLUSIVE', 0, 1, 1, NOW()),
(4, 1, 4, 'COMPATIBLE', 0, 1, 1, NOW());
```
### 6.2 评定规则示例数据
```sql
-- 一等学业奖学金评定规则
INSERT INTO jcbt_scholarship_rule (id, rule_code, rule_name, scholarship_type_id, rule_type, evaluation_method, min_score, tenant_id, create_by, create_time) VALUES
(1, 'ACADEMIC_FIRST_RULE', '一等学业奖学金评定规则', 1, 'AUTO', 'SCORE', 90.00, 1, 1, NOW());
-- 规则条件配置
INSERT INTO jcbt_scholarship_rule_condition (id, rule_id, condition_group, field_name, field_type, operator_type, condition_value, weight, error_message, tenant_id, create_by, create_time) VALUES
(1, 1, 'GROUP_MAIN', 'gpa', 'NUMBER', 'GTE', '3.7', 0.50, 'GPA需≥3.7', 1, 1, NOW()),
(2, 1, 'GROUP_MAIN', 'moral_score', 'NUMBER', 'GTE', '85', 0.30, '德育分需≥85分', 1, 1, NOW()),
(3, 1, 'GROUP_MAIN', 'credit_completed', 'NUMBER', 'GTE', '30', 0.10, '已修学分需≥30', 1, 1, NOW()),
(4, 1, 'GROUP_MAIN', 'disciplinary_action', 'BOOLEAN', 'EQ', 'false', 0.10, '不能有违纪记录', 1, 1, NOW());
```
---
## ⚡ 7. 性能优化建议
### 7.1 查询优化
- **分页查询**: 使用LIMIT和OFFSET，避免深度分页
- **索引覆盖**: 设计覆盖索引减少回表查询
- **条件筛选**: 优先使用选择性高的条件
- **统计查询**: 使用Redis缓存统计结果
### 7.2 数据归档
```sql
-- 历史数据归档策略
CREATE TABLE jcbt_scholarship_application_archive LIKE jcbt_scholarship_application;
-- 定期归档3年前的数据
INSERT INTO jcbt_scholarship_application_archive
SELECT * FROM jcbt_scholarship_application
WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 YEAR);
```
### 7.3 缓存策略
- **奖学金类型**: Redis缓存，TTL=24小时
- **评定规则**: Redis缓存，TTL=12小时
- **名额统计**: Redis缓存，TTL=30分钟
- **学生申请数**: Redis缓存，TTL=10分钟
---
## 📋 8. 扩展性考虑
### 8.1 字段扩展
- 所有表预留`ext_fields` JSON字段用于业务扩展
- 规则条件支持自定义字段类型
- 审核流程支持多级审核配置
### 8.2 多租户支持
- 所有表包含`tenant_id`字段
- 数据查询强制添加租户过滤条件
- 支持租户级别的配置隔离
### 8.3 国际化支持
- 字典数据支持多语言
- 错误提示信息支持国际化
- 日期时间支持时区配置
---
**设计说明**: 本数据模型设计充分考虑了奖学金管理的复杂业务场景，通过灵活的规则引擎设计支持各种评定条件，同时遵循项目的技术规范，确保系统的可扩展性和可维护性。
Reference in new issue View Git Blame Copy Permalink