Rails/通用SQL EAV模式下城市许可与位置进度关联数据库模型设计问询

你的现有设计不属于反模式，是符合关系型数据库第三范式的标准实现，逻辑自洽：

• 基础关联层：permit许可表、requirements要求表通过permit_requirements多对多关联表绑定，灵活支持不同许可配置差异化的要求列表，没有冗余存储。
• 位置关联层：location位置表通过location_permits多对多关联表和许可绑定，支持按位置/州筛选对应需要的许可列表，符合你的业务场景需求。
• 状态存储层：新增的location_requirement_answers表以location_id+requirement_id为唯一标识存储每个位置对应要求的完成状态，关联逻辑正确，不存在数据冲突问题。
  注意：你需要给所有多对多关联表设置联合主键，比如permit_requirements联合主键为(permit_id, requirement_id)，location_requirement_answers联合主键为(location_id, requirement_id)，避免生成重复的关联数据。

你要同时拉取指定位置的完整许可信息、对应要求、要求完成状态，直接使用多表左关联查询即可，参考SQL如下：

SELECT
  p.id permit_id, p.type permit_type, p.state permit_state,
  r.id requirement_id, r.key requirement_key, r.value requirement_content,
  COALESCE(lra.status, '未开始') AS requirement_status
FROM location l
INNER JOIN location_permits lp ON l.id = lp.location_id
INNER JOIN permit p ON lp.permit_id = p.id
INNER JOIN permit_requirements pr ON p.id = pr.permit_id
INNER JOIN requirements r ON pr.requirement_id = r.id
LEFT JOIN location_requirement_answers lra 
  ON l.id = lra.location_id AND r.id = lra.requirement_id
WHERE l.id = 【目标位置ID】

该查询可以一次性返回所有需要的数据，左关联location_requirement_answers可以处理还未上报状态的要求，默认返回未开始状态，不会出现数据遗漏。数据量较大时给所有关联字段加普通索引即可保证查询性能。

不需要盲目新增该表，根据你的业务逻辑判断即可：

• 若许可的整体状态完全由下属要求的状态聚合生成（比如所有要求状态为完成则许可整体完成），不需要新增该表，直接在业务层聚合location_requirement_answers的状态即可，不会和现有逻辑重复。
• 若许可存在独立于要求的整体状态（比如所有要求都完成后，许可还需要走官方审核流程，有独立的审核中/通过/驳回状态），则可以新增location_permit_answers表，存储location_id+permit_id对应的许可整体状态，两者的存储逻辑完全独立，不存在冲突。

• 给location_requirement_answers表新增update_time、operator字段，记录状态变更的时间和操作人，方便后续进度追溯。
• 如果存在同一许可在不同州要求不同的场景，可以在permit_requirements表新增state字段，绑定州级的差异化要求，避免重复创建相同类型的permit数据。

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