关于数据库存储的完善-山大软院创新实训项目博客_7

一、项目背景

作为英语学习AI应用的核心数据存储层，我们设计了三个关键数据表来支持系统的主要功能：用户管理、故事生成和语音评估。本文将详细记录数据库设计思路、技术决策和实现过程。本次是经过团队成员沟通后进行完善。

 

二、数据库整体架构

我们的系统采用MySQL关系型数据库，包含三个核心表：

1. users - 用户基础信息表

2. story - 故事生成记录表

3. speech_score - 语音评估成绩表

 

三、用户表(users)设计详解

表结构设计

CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(50) NOT NULL COMMENT '用户名，唯一',
  `password` varchar(100) NOT NULL COMMENT '密码（可明文存储，建议后期加密）',
  `nickname` varchar(100) DEFAULT NULL COMMENT '昵称',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱地址',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `gender` varchar(10) DEFAULT NULL COMMENT '性别',
  `interests` mediumtext COMMENT '兴趣关键词，逗号分隔',
  `grade` varchar(20) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=1932075118951956482 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

设计思考

1. 主键选择：使用自增bigint作为主键，满足长期发展需求

2. 安全考虑：

   • 用户名设置唯一约束

   • 密码字段预留100字符空间为未来加密做准备

3. 扩展性：

   • 包含基础个人信息字段

   • interests字段使用mediumtext存储兴趣标签

   • grade字段记录用户英语水平等级

4. 字符集：采用utf8mb4支持完整Unicode字符（如emoji）

技术决策

• 存储引擎：选择InnoDB支持事务和外键约束

• 自增初始值：设置为较大的数值避免测试数据干扰

• 注释完善：每个字段添加详细注释方便维护

 

四、故事表(story)设计详解

表结构设计

CREATE TABLE `story` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `input_prompt` mediumtext COLLATE utf8mb4_unicode_ci,
  `output_story` longtext COLLATE utf8mb4_unicode_ci,
  `translated_story` text COLLATE utf8mb4_unicode_ci,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `language` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_user` (`user_id`),
  CONSTRAINT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1933529848442781699 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

设计思考

1. 关系设计：

   • 与users表建立外键关系

   • 设置ON DELETE CASCADE实现级联删除

2. 内容存储：

   • input_prompt: 用户输入的提示词(mediumtext)

   • output_story: AI生成的完整故事(longtext)

   • translated_story: 翻译后的故事内容

3. 元数据：

   • language字段记录故事语言

   • title字段方便用户识别故事

技术决策

• 字符集：使用utf8mb4_unicode_ci排序规则支持多语言排序

• 文本类型选择：

  • 输入提示词用mediumtext(最大16MB)

  • 输出故事用longtext(最大4GB)

• 索引设计：在user_id上建立索引优化查询性能

 

五、语音评分表(speech_score)设计详解

表结构设计

CREATE TABLE `speech_score` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `story_topic` varchar(255) CHARACTER SET latin1 NOT NULL,
  `recognized_text` text CHARACTER SET latin1,
  `accuracy_score` bigint(20) DEFAULT NULL,
  `fluency_score` bigint(20) DEFAULT NULL,
  `completeness_score` bigint(20) DEFAULT NULL,
  `pron_score` bigint(20) DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `speech_score_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1933747560288968706 DEFAULT CHARSET=utf8mb4;

设计思考

1. 评分维度：

   • 准确度(accuracy_score)

   • 流利度(fluency_score)

   • 完整度(completeness_score)

   • 发音分数(pron_score)

2. 关联设计：

   • 与users表建立外键关系

   • 记录关联的story_topic

3. 识别文本：存储语音识别结果用于后续分析

技术决策

• 字符集：识别文本使用latin1减少存储空间

• 分数类型：使用bigint确保足够的分值范围

• 时间记录：自动记录创建时间用于学习进度分析

 

六、数据库实现过程中的挑战与解决方案

1. 要不要修改？

问题：一开始我设计了6个表，也进行了repository，model，等架构层的实现，但是和队员的方向出现了不一致性。
解决方案：统一意见，听从开发后端队友的建议，决定摒弃掉一些数据实体，优化数据库设计。

2. 大文本存储优化

问题：故事内容可能很长，影响查询性能
解决方案：

• 关键查询不直接包含大文本字段

• 实现分页查询机制

• 考虑将大文本分离到单独表(最终未采用，因查询模式简单)

3. 外键约束的考量

决策过程：

• 初期考虑不使用外键，完全由应用层控制

• 最终添加外键确保数据完整性

• 为story表设置ON DELETE CASCADE实现级联删除

CONSTRAINT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE

4. 自增ID的起始值

设计考量：

• 设置较大的初始值(19位)

• 避免测试数据与生产数据ID冲突

• 为数据迁移预留空间

性能优化措施

1. 索引策略：

   • 所有外键字段建立索引

   • 用户名设置唯一索引

2. 字段类型优化：

   • 精确选择文本类型(TEXT, MEDIUMTEXT, LONGTEXT)

   • 数值字段使用恰当范围(bigint)

3. 存储引擎：统一使用InnoDB支持事务

七、结语

通过这次数据库设计实践，我们构建了一个既能满足当前需求，又具备良好扩展性的数据存储层。合理的表结构设计和关系建模为应用功能提供了坚实基础，而性能优化措施则确保了系统在大数据量下的稳定运行。随着业务发展，我们将持续优化数据库架构，为用户提供更流畅的英语学习体验。