如何基于下拉选择为Category等关联表插入关联ID及名称关联方案咨询

方案1：保留原ID关联逻辑的插入实现 #

你当前的原表结构用ID关联是关系型数据库的标准设计，插入逻辑可按以下步骤执行：

• 第一步：先插入Category表数据，拿到生成的自增ID值
• 第二步：插入Subcategory表数据时，把上一步拿到的Category ID填到cat_id字段，插入后拿到Subcategory的自增ID
• 第三步：插入CatOption表数据时，把上一步拿到的Subcategory ID填到sub_id字段即可

你给出的示例对应的SQL插入语句参考如下：

-- 插入分类
INSERT INTO Category (Category) VALUES ('Sky');
-- 假设上面插入返回的ID是1，插入子分类
INSERT INTO Subcategory (cat_id, sub) VALUES (1, 'Blue'), (1, 'Grey');
-- 假设第一个子分类Blue返回的ID是1，插入选项
INSERT INTO CatOption (sub_id, caption) VALUES (1, 'sunny'), (1, 'with clouds');

如果是前端交互场景，你已经做好的三个下拉选可以直接把下拉选项的value设为对应记录的ID：用户选完分类后，子分类下拉筛选cat_id = 所选分类ID的记录；子分类选完后选项下拉筛选sub_id = 所选子分类ID的记录，插入数据时直接拿选中的ID存入对应字段即可。

方案2：用名称关联的实现方式 #

你提到的用名称关联的思路可以实现，但不推荐在生产环境使用，具体实现方法和弊端如下：

实现步骤 #

1. 先调整表结构：
   • Subcategory表的cat_id字段类型改成和Category表的Category字段一致的字符串类型
   • CatOption表的sub_id字段类型改成和Subcategory表的sub字段一致的字符串类型
2. 插入逻辑和ID关联逻辑类似，仅需把存储ID改为存储对应名称即可：

-- 插入分类
INSERT INTO Category (Category) VALUES ('Sky');
-- 插入子分类，直接存储分类名称
INSERT INTO Subcategory (cat_id, sub) VALUES ('Sky', 'Blue'), ('Sky', 'Grey');
-- 插入选项，直接存储子分类名称
INSERT INTO CatOption (sub_id, caption) VALUES ('Blue', 'sunny'), ('Blue', 'with clouds');

3. 关联查询时直接用名称匹配即可：

-- 查询Sky分类下的所有子分类
SELECT * FROM Subcategory WHERE cat_id = 'Sky';

弊端说明 #

• 名称重复会直接导致关联混乱，比如出现两个同名分类时，子分类无法匹配到正确的父级记录
• 名称修改成本极高，比如要把Sky改成天空，需要同步修改Subcategory表里所有对应的cat_id值，漏改就会导致关联断裂
• 字符串关联的查询性能远低于整型ID，数据量增大后性能差异会非常明显

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