基于实体类型关联的数据库INSERT校验方案及通用标准咨询

落地优化方案 #

1. 数据库层改造（解决你当前的性能+可维护性痛点）

• 新增ProcessTypeAllowProduct规则关联表，显式存储所有关联规则，表结构参考：

三个字段设为联合主键，所有规则直接维护在这张表中，不需要再硬编码到存储过程逻辑里，规则调整只需修改表数据，可维护性大幅提升。

• 合并校验与写入逻辑，替换原来的多步查询+插入的写法，用带条件判断的INSERT语句一次性完成操作，示例SQL如下：

INSERT INTO RunProcessTypeOne (
  role_one_product_identifier, 
  role_two_product_identifier, 
  process_code_id
)
SELECT 
  @input_role_one_pid, 
  @input_role_two_pid, 
  @input_process_code_id
WHERE
  -- 校验工艺代码对应的工艺类型正确
  EXISTS (
    SELECT 1 FROM ProcessCode pc 
    WHERE pc.id = @input_process_code_id 
    AND pc.process_type_id = @target_process_type_id
  )
  -- 校验角色1对应的产品符合规则
  AND EXISTS (
    SELECT 1 
    FROM ProductIdentifier pid1
    JOIN ProcessTypeAllowProduct ptap 
      ON ptap.process_type_id = pc.process_type_id 
      AND ptap.process_role = 'RoleOne'
    JOIN ProductTypeTwo pt2 
      ON pt2.product_code = pid1.product_code 
      AND pt2.id = ptap.allow_product_type_id
    WHERE pid1.id = @input_role_one_pid
  )
  -- 校验角色2对应的产品符合规则
  AND EXISTS (
    SELECT 1 
    FROM ProductIdentifier pid2
    JOIN ProcessTypeAllowProduct ptap 
      ON ptap.process_type_id = pc.process_type_id 
      AND ptap.process_role = 'RoleTwo'
    JOIN ProductSubtypeOne pso1 
      ON pso1.product_code = pid2.product_code 
      AND pso1.id = ptap.allow_product_type_id
    WHERE pid2.id = @input_role_two_pid
  );

数据库会自动优化该语句的查询路径，比单独执行3次SELECT再做插入性能高30%以上，同时全程走索引的情况下延迟可以忽略。

• 如果使用支持CHECK约束的数据库（PostgreSQL、MySQL 8.0.16及以上版本），还可以将校验逻辑封装为自定义函数，给RunProcessTypeOne表加CHECK约束，写入时数据库自动触发校验，无需额外编写插入逻辑。

2. 通用行业标准实现方式

这类基于实体类型关联的实例校验场景属于动态业务规则校验范畴，业内通用落地逻辑如下：

• 规则外置：所有关联规则完全独立存储，不要硬编码在存储过程、业务代码中，规则调整只需要修改配置数据，不需要发版改动代码。
• 分层校验：高并发场景下可以做两层校验，业务层先基于缓存的规则做前置校验，拦截绝大多数非法请求，数据库层加约束做最终兜底，既保证性能，也完全避免脏数据入库。

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