MySQL建筑师与设计师关联表设计及查询SQL正确性咨询

一、表结构设计评估 #

现有结构合理性：

• 多对多关联逻辑正确：architect和designer为多对多关系，通过中间表connections做关联，两个外键分别对应两张业务表的userId主键，完全满足「一个architect可添加多个designer、一个designer可被多个architect添加」的业务需求，符合类Facebook好友连接的基础逻辑。

可优化点：

• 缺少请求状态相关字段：你提到是类似好友请求的机制，目前connections表仅存储关联ID，没有请求状态（待审核/已通过/已拒绝）、发起时间、处理时间等字段，如果后续需要做申请审核、历史记录查询等功能，现有结构无法支撑。
• 字段命名不统一：connections表的userID、designersId命名和另外两张表的userId驼峰风格不一致，且userID语义模糊，建议修改为architect_user_id、designer_user_id，降低后续维护的理解成本。
• 生产环境提示：如果是线上使用，两张用户表的password字段禁止明文存储，需加密后保存。

二、SQL语句规范评估 #

现有SQL正确性：你当前编写的SQL可以得到正确结果，逻辑通顺，对于INNER JOIN来说，把architect ID的筛选条件放在ON子句和放在WHERE子句的执行结果没有差异。

可优化的规范点：

• 建议给表设置别名，简化语句长度，提升可读性
• 建议将表关联逻辑和业务筛选逻辑拆分：INNER JOIN的ON子句仅放两张表的关联条件，业务筛选条件统一放在WHERE子句中，语义更清晰，后续修改维护更方便
• 若userId字段为数值类型，建议去掉值两端的引号，避免数据库隐式类型转换影响索引查询效率

优化后的SQL参考：

SELECT 
    d.username,
    d.location,
    d.job,
    d.rating,    
    c.designersId 
FROM designer d
INNER JOIN connections c 
    ON d.userId = c.designersId 
WHERE c.userId = '指定architect的用户ID'

额外注意：你当前SQL中查询了designer.contact字段，但你给出的designer表字段列表中不存在contact字段（该字段属于architect表），请确认是否写错字段，避免后续执行报错。

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