相关视频数据库表结构

Question

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

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

因此，假设将带有ID = 5的视频添加为具有ID = 10的视频的相关视频，则表结构将为

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

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

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

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

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

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

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

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

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

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

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

Answer 1

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

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

您需要存储的附加数据的结构简单且定义良好，并且对应于基本的数据库规范化模式，因此实际上没有理由遵循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)
);