许可证类别数据存储设计咨询：兼顾数据库规范化与业务需求

看起来你之前的设计踩了个典型的坑——把类别定义数据和具体实例数据混在了一起，lut_training应该是纯类别配置表，不该放和具体人员绑定的许可证编号、有效期这类字段。下面给你一套既符合数据库规范化原则，又能完美适配业务需求的设计方案：

核心思路：拆分"类别"与"实例" #

我们需要明确两个核心概念：

• 许可证类别：是静态的分类定义（比如1F=武装护卫），属于系统配置数据，不随人员变化而变化，可随时增减类别。
• 人员持有的许可证实例：是每个人员实际持有的、带唯一编号和有效期的具体证件，每个实例对应一个类别，支持动态添加/移除。

1. 修正lut_training表（纯类别配置） #

保留它作为lookup表，只存类别相关的静态数据，去掉所有和人员、具体许可证相关的字段：

CREATE TABLE `lut_training` (
 `training_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `short_name` varchar(10) NOT NULL UNIQUE, -- 例如"1F"，保证类别短名唯一
 `long_name` varchar(100) NOT NULL, -- 例如"1F Armed Guard"
 PRIMARY KEY (`training_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2. 重新设计licences表（人员许可证实例） #

这个表用来存储每个人员持有的具体许可证，每个行对应一个"人员-类别"的许可证实例，包含编号、有效期等实例数据：

CREATE TABLE `licences` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `person_id` int(10) unsigned NOT NULL,
 `training_id` int(10) unsigned NOT NULL,
 `licence_number` varchar(50) NOT NULL UNIQUE, -- 政府颁发的唯一编号
 `expiry_date` date NOT NULL,
 `is_active` tinyint(1) NOT NULL DEFAULT 1, -- 软删除标记，替代直接删除，方便保留历史记录
 PRIMARY KEY (`id`),
 -- 可选：确保同一人员同一类别只能有一个有效许可证（如果允许过期后重新申请，可去掉此约束）
 UNIQUE KEY `person_training_active_unique` (`person_id`, `training_id`, `is_active`) 
    WHERE (`is_active` = 1),
 KEY `person_id` (`person_id`),
 KEY `training_id` (`training_id`),
 CONSTRAINT `licences_ibfk_person` FOREIGN KEY (`person_id`) REFERENCES `people` (`person_id`) 
    ON UPDATE CASCADE ON DELETE CASCADE,
 CONSTRAINT `licences_ibfk_training` FOREIGN KEY (`training_id`) REFERENCES `lut_training` (`training_id`) 
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

为什么这个设计适合你的业务？ #

• 完全符合1NF：所有字段都是原子值，没有多值存储的问题，避免了字符串/位运算的更新麻烦。
• 支持动态调整类别：
  • 给人员添加新类别：插入一条新的licences记录即可。
  • 移除人员的某个类别：将对应记录的is_active设为0（软删除，保留历史），或者直接删除（如果不需要历史）。
• 清晰区分配置与实例：lut_training的类别增减不会影响已有的人员许可证数据，新增类别后直接在licences里关联即可。
• 查询高效灵活：比如要查询某个人的所有有效许可证：

  SELECT t.short_name, t.long_name, l.licence_number, l.expiry_date
  FROM licences l
  JOIN lut_training t ON l.training_id = t.training_id
  WHERE l.person_id = 123 AND l.is_active = 1;
  

为什么不推荐单字段存储多类别？ #

你提到的varchar/整数位存储方案虽然节省空间，但有致命缺陷：

• 违反1NF，后续查询、维护会非常麻烦（比如要筛选持有1A的人员，得用模糊匹配或位运算，效率低且容易出错）。
• 根本无法存储每个类别对应的独立许可证编号和有效期——这是你业务的核心需求，单字段方案完全无法满足。

可选优化建议 #

• 如果需要跟踪许可证状态变化（比如从有效到过期、重新申请），可以新增licence_status_history表，记录状态变更的时间、操作人、原因等。
• 可以添加issue_date字段记录许可证的发证日期，完善数据维度。

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