数据库设计：如何关联Product-Sales与Modifiers两类中间表？

1. Product-Sales与Modifiers的关联方式选择 #

两种设计各有适用场景，但关联Product-Sales与Product-Modifiers更符合业务逻辑的严谨性：

• 若采用粉色表直接关联Product-Sales与Modifiers：
  优点是结构简单，直接记录订单商品对应的修饰符；但缺点是无法追溯该修饰符在下单时是否属于该商品的合法组合——如果后续Product-Modifiers中移除了该商品与修饰符的关联，历史订单的合法性就无法验证。
• 若关联Product-Sales与Product-Modifiers：
  优点是能明确留存“下单时该修饰符是商品允许使用的合法组合”这一关键上下文，历史订单的逻辑完整性可查；虽然多了一层关联，但更贴合“修饰符是依附于商品的可选组合存在”的业务本质。

如果业务允许订单记录“非商品当前合法的修饰符”（比如特殊定制场景），直接关联Modifiers也可，但绝大多数零售类场景建议优先选择关联Product-Modifiers的方案。

2. 重复订单时判断Modifier当前有效性的方法 #

假设当前采用的是粉色表（关联Product-Sales与Modifiers）的设计，判断逻辑如下：

• 从历史订单的Product-Sales-Modifiers中提取目标商品ID（psa_prdfk）和修饰符ID（psm_modfk）。
• 查询Product-Modifiers表，检查是否存在pm_prdfk = [目标商品ID] AND pm_modfk = [目标修饰符ID]的有效记录：
  • 若表中有is_active（是否激活）、expire_date（过期时间）这类状态字段，需额外加上is_active = 1或expire_date > NOW()这类条件。
  • 存在匹配记录则说明该修饰符当前对商品仍有效，不存在则无效。

如果采用的是关联Product-Modifiers的设计，只需检查对应的Product-Modifiers记录是否仍处于有效状态即可。

3. 查询语句的问题与优化建议 #

当前的LEFT JOIN逻辑确实存在可优化点，建议在Product-Sales-Modifiers中存储pm_pk（Product-Modifiers的主键），原因及现有疏漏如下：

为什么要存储pm_pk？ #

• 精准定位历史组合：如果Product-Modifiers中同一商品+修饰符的组合存在多条记录（比如软删除的旧版本、不同有效期的组合），通过pm_prdfk+pm_modfk关联会返回重复行，而存储pm_pk可以直接关联到下单时使用的那一条具体组合，避免歧义。
• 简化查询逻辑：存储后JOIN条件可简化为LEFT JOIN Product-Modifiers ON pm_pk = psm_pm_pk，代码更简洁，查询效率也更高。

当前查询的疏漏 #

1. 上下文丢失风险：如果后续Product-Modifiers中删除了该商品+修饰符的组合，当前查询会关联不到任何记录，导致历史订单中使用过的修饰符失去了“当时合法”的关联依据。
2. 未处理状态过滤：若Product-Modifiers有状态字段（如是否停用），当前查询会关联到所有匹配的记录（包括无效的），无法精准筛选出当前有效的组合。
3. 性能隐患：通过两个字段（pm_prdfk和pm_modfk）进行关联，在数据量较大时，查询效率远低于单主键关联。

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