mysql - 相关视频 数据库表结构

Question

我正在尝试找出最适合相关视频的表格结构。我有一个表videos，其中包含有关所有视频的详细信息。我正在计划推出视频推荐的新功能，目前我将手动创建该功能。

我正在考虑将其存储在视频表 videos.latedVideos 内的表列中，并存储相关视频 ID 的 JSON 数据。但是，我也希望它能够向后兼容。

因此，如果将 ID = 5 的视频添加为 ID = 10 视频的相关视频，则表结构将为

   | id | videoId | ..... | relatedVideos|

   | 1  | 10      | ..... | [5]          |
   | 2  | 4       | ..... | [3,6,7]      |

我还希望 ID = 10 的视频充当 ID = 5 视频的相关视频。一种方法是当我为 #10 创建条目时，在 videos 表上为 #5 创建一个新条目

   | id | videoId | ..... | relatedVideos|

   | 1  | 10      | ..... | [5]          |
   | 2  | 4       | ..... | [3,6,7]      |
   | 3  | 5       | ..... | [10]         |

但我正在寻找更合适的东西，在那里我可以绘制视频之间的多对多关系。

一种选择是创建一个表 latedVideos 并为每个与主视频相关的相关视频创建新条目，但在添加/编辑相关视频时可能会导致重复记录任何特定视频。

|id | videoID | relatedVideoID|
|1  | 5       | 10            |
|2  | 5       | 15            |
|3  | 5       | 13            |
|4  | 13      | 19            |

现在，我正在做的是，如果我正在浏览视频页面#13，我将执行两个查询(Select relatedVideoID from relatedVideos where videoID = 13) 和(从 relatedVideos 中选择 videoID，其中 relatedVideoID = 13)。我相信我可以使用 SQL 连接或联合做类似的事情，我还没有探索这些选项。

然后，我将两个数组结果合并以获得 [19, 5] (我只保留不同的 ID)。这就是完成工作，但是，我真的很期待实现更合适的解决方案。

只是寻找一些关于什么是好的方法的建议。

Answer 1

我建议采用最后列出的选项，即设置一个桥接表来存储视频之间的 N-M 关系。

一般来说，关系数据库不擅长管理 JSON 或 CSV 数据(尽管其中一些具有很好的供应商功能)。基本的 SQL 查询很快就会变得更难编写，而且性能也会受到影响( here 是一篇关于在数据库列中存储 CSV 的著名 SO 文章)。

您需要存储的附加数据的结构简单且定义明确，并且对应于基本的数据库规范化模式，因此确实没有理由遵循 JSON 或 CSV 路径。

旁注:为了保持数据完整性并避免重复，您需要确保没有存储像 (1, 2)、(2, 1) 这样的对。如果您运行的是 MySQL 8.0，则可以为此使用检查约束。表的 DDL 可能如下所示:

create table related_videos (
    video_id1 int,
    video_id2 int,
    check(video_id1 < video_id2),
    primary key(video_id1, video_id2),
    constraint fk_related_video1 foreign key (video_id1) references video(id),
    constraint fk_related_video2 foreign key (video_id2) references video(id)
);