Firestore词汇数据转MySQL表结构设计及校验方案咨询

现有Schema合理性评估 #

你当前的设计存在两处核心缺陷：

1. 丢失了业务要求的「译义归属对应原义」的层级关联关系，无法区分不同原义对应的译义，不符合实际业务逻辑
2. 原义和译义共用同一张关联表通过enum标记类型，后续的重复判定、最少条数校验的实现成本都很高，整体可扩展性差

优化后的Schema设计 #

针对业务需求，推荐采用分层关联的表结构，如下：

• vocabulary_list表：存储词汇列表的基础信息，主键为list_id
• vocabulary表：保留你原有设计的逻辑，主键为vocab_id，外键关联list_id，存储测试得分、答对答错次数等字段
• word表：保留原有设计，主键为word_id，给单词内容字段添加唯一索引，避免相同文本重复存储
• original_word表：存储词汇对应的原义关联，主键为original_word_id，外键关联vocab_id与word_id，支持单个词汇关联多个原义
• translation_word表：存储原义对应的译义关联，主键为translation_word_id，外键关联original_word_id与word_id，既实现了译义归属对应原义的规则，也支持单个原义关联多个译义

核心问题解决方案 #

1. 重复词汇条目检测 #

针对两种常见重复场景分别处理：

• 单个单词文本重复：通过word表单词内容字段的唯一索引，插入新单词时若已存在则直接复用已有word_id，避免相同单词重复入库
• 完整词汇条目重复：业务层插入前做前置校验，查询目标词汇列表下是否存在原义集合、对应译义集合完全匹配的词汇条目，匹配即判定为重复，跳过插入逻辑即可

2. 保证每个词汇至少1个原义、1个译义 #

采用事务+分层校验的方式实现：

• 所有词汇创建操作均用数据库事务包裹，创建vocabulary记录时，必须同时插入至少1条original_word记录、以及对应至少1条translation_word记录，任意一步操作失败则事务整体回滚，不会产生脏数据
• 若使用PostgreSQL等支持约束触发器的数据库，可以额外添加触发器作为兜底校验，校验vocabulary表的每条记录对应的original_word计数≥1、original_word关联的translation_word计数≥1，不符合规则则拒绝写入、更新、删除操作

校验逻辑归属判断 #

优先将所有校验逻辑放在后端业务层实现，不推荐使用存储过程：

• 后端代码的可读性、可维护性更高，规则迭代不需要修改数据库结构，方便做版本控制
• 存储过程的调试、迁移成本极高，不同数据库语法不兼容，后续更换数据库的改造量极大
• 仅在对数据一致性要求极高的场景下，可以把触发器作为兜底校验手段，避免业务代码bug产生脏数据，核心逻辑仍然保留在后端

内容的提问来源于stack exchange，提问作者user12809117