如何对演员、电影、制片人关联表合理分解实现最优查询

核心逻辑说明 #

你之前设计ActorMovie关联表的思路是正确的，未达到预期效果的核心原因是原Movie表还保留了ActorName、ProducerName这类冗余关联字段，且缺少制片人与电影的多对多关联表，调整方案完全符合数据库第三范式，无冗余数据，查询性能最优。

第一步：清理主表冗余字段 #

调整后三张主表仅存储实体本身的独立信息，不存储关联数据：

• Actor表（演员主表）
  • Id：主键，唯一标识演员
  • ActorName：演员姓名
  • Bio：演员简介
• Producer表（制片人主表）
  • Id：主键，唯一标识制片人
  • ProducerName：制片人姓名
  • Bio：制片人简介
• Movie表（电影主表）
  • Id：主键，唯一标识电影
  • Name：电影名称
  • Description：电影描述

第二步：新增多对多关联表 #

关联表无需额外自增主键，使用联合主键即可避免重复关联，同时提升查询性能：

• movie_actor关联表（存储电影和演员的多对多关系）
  • movie_id：外键，关联Movie.Id
  • actor_id：外键，关联Actor.Id
  • 联合主键：(movie_id, actor_id)，禁止同一部电影重复关联同一个演员
• movie_producer关联表（存储电影和制片人的多对多关系）
  • movie_id：外键，关联Movie.Id
  • producer_id：外键，关联Producer.Id
  • 联合主键：(movie_id, producer_id)，禁止同一部电影重复关联同一个制片人

常用查询示例 #

查询某部电影的所有参演演员 #

SELECT a.ActorName, a.Bio
FROM Movie m
JOIN movie_actor ma ON m.Id = ma.movie_id
JOIN Actor a ON ma.actor_id = a.Id
WHERE m.Name = '目标电影名称';

查询某个演员参演的所有电影 #

SELECT m.Name, m.Description
FROM Actor a
JOIN movie_actor ma ON a.Id = ma.actor_id
JOIN Movie m ON ma.movie_id = m.Id
WHERE a.ActorName = '目标演员姓名';

查询某部电影的所有制片人 #

SELECT p.ProducerName, p.Bio
FROM Movie m
JOIN movie_producer mp ON m.Id = mp.movie_id
JOIN Producer p ON mp.producer_id = p.Id
WHERE m.Name = '目标电影名称';

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