mysql - 两个关系表的MySQL数据库架构

Question

我正试图为一个特定的数据结构考虑最有效的数据库模式。主要有两个实体：课程和主题。课程是主题的集合。主题包含视频、资源和视频总时间等字段。
直观地表示此数据结构：

- Course
|_ ID: 12345
|_ Themes: [A, B] (an array of UIDs)

- Theme A
  |_ Courses: [12345,67890] (an array of UIDs)
  |_ Videos: [1,2,3,4,5,7] (an array of UIDs)
  |_ Resources: [10,11,12] (an array of UIDs)
  |_ Video Total Time: 10000 (probably stored as seconds as tinyint field)
- Theme B
  |_ Courses: [12345,98765] (an array of UIDs)
  |_ Videos: [5,6,7,8] (an array of UIDs)
  |_ Resources: [12,13,14] (an array of UIDs)
  |_ Video Total Time: 20000 (probably stored as seconds as tinyint field)

我要实现的是两个表的数据库模式，一个用于课程，一个用于主题。我们的想法是使用一个MySQL查询来获取课程并对主题中的所有字段进行分组。换句话说，当我得到MySQL查询的结果时（使用PHP），我将得到如下数组或对象：

Array(
  'ID' => 12345
  'themes' => [A,B]
  'videos' => [1,2,3,4,5,6,7,8]
  'resources' => [10,11,12,13,14]
  'video_total_time' => 30000
)

所以，重点是它们是两个关系数据库。当我向数据库发送请求视频数据的查询时，我需要从所有主题中提取数据，并将它们合并在一起。
因为我不是SQL/MySQL方面的专家，所以我试着在了解它的同时了解一下：
1）这两个实体的最佳数据库模式是什么？课程和主题？对表演的特殊思考
2）我能用SQL得到最终的数据吗？或者我应该从数据库中提取一些数据，然后用PHP解析这些数据？什么通常更快？
3）存储一系列uid的最佳方法是什么？作为一根绳子？或者有更好的方法来储存它？
它的主要目标是性能。我在一个不同的数据库模式中有这类数据，与成千上万种其他类型的数据（WP数据库、WP posts/WP postemta表）合并在一起，但是现在获取所需信息的速度真的很慢。
任何提示和建议都是非常欢迎的！
编辑：解决了！
决定哪一个答案最适合我的需要是一个艰难的决定，因为“蒂莫顿”和“保尔斯皮格尔”的答案将我们引向同一条道路，但方法略有不同。Tim的回答对于理解如何正确设计数据库模式、考虑多对多关系以及如何组织查询非常有用。但是，由于这个问题的主要焦点是提高性能，Paul的答案更集中在这一点上，并提供了关于主键和索引（这是提高查询性能的基础）的具体细节。
无论如何，我学到了很多关于设计数据库模式的知识。以下是我学到的教训：
不要把所有东西都塞进同一个表中：在定义所需的表之前，正确地标识实体是最基本的。我从两张桌子开始，视频和主题。但事实证明，适合我的规范的数据库模式包括用于视频和资源的表。
不要将数组存储到列中：使用适当的策略定义实体之间的关系。如果有一对一或一对多关系，请使用实体id和外键。如果有多对多关系，则正确的设计模式是创建专用表，仅用于创建实体之间的关系。这将允许您在查询中使用JOIN子句将所有数据放在一起。
考虑性能时，考虑索引：根据表结构的不同，使用索引或组合索引可以提高查询性能。
不要试图在一个大查询中得到所有东西：当然可以，但是对需要的部分数据进行单独的查询（在我的示例中，一个查询获取课程的所有主题，一个查询获取课程的所有视频，一个查询获取课程的资源）会获得代码组织和可读性的回报。
我不知道我对上面的一切是否正确，但这是我迄今为止学到的。希望这也能帮助别人。

Answer 1

创建架构
步骤1：标识实体及其属性
课程（ID、标题、说明）
主题（ID、标题、说明）
视频（ID、标题、说明、持续时间）
资源（ID、标题、url）
步骤2：确定关系
主题=>课程
视频=>主题
资源=>主题
步骤3：创建表
课程
ID（主键）
标题
描述
主题
ID（主键）
课程编号（FK）
标题
描述
视频
ID（主键）
主题id（FK）
标题
描述
持续时间
资源
ID（主键）
主题id（FK）
标题
网址
如果主题可以共享视频和资源，那么它将是多对多的关系。
在这种情况下，这些关系需要单独的表。
从theme_id和videos中删除ressources列，并添加以下表格：
主题视频
主题id（PK）（FK）
视频id（PK）（FK）
主题资源
主题id（PK）（FK）
资源id（PK）（FK）
在这里，您应该在(theme_id, video_id)和(theme_id, ressource_id)上定义复合主键。
同时在(video_id, theme_id)和(ressource_id, theme_id)上创建反向索引。
检索数据
假设你知道课程的ID（123），
然后可以检索相关数据（从多对多模式）
使用以下查询（一个接一个执行）：

select c.*
from courses c
where c.id = 123;

select t.*
from themes t
where t.course_id = 123;

select distinct v.*
from themes t
join themes_videos tv on tv.theme_id = t.id
join videos v on v.id = tv.video_id
where t.course_id = 123;

select distinct r.*
from themes t
join themes_ressources tr on tr.theme_id = t.id
join ressources r on r.id = tr.ressource_id
where t.course_id = 123;

然后用PHP从检索到的数据合成数组/对象。
性能
尝试用一个SQL查询获取所有数据并不总是一个好主意。
你只是让你的代码和模式太复杂了。
执行几个查询并不是世界末日。
应该避免的是在循环中运行查询
（例如：为每个主题选择相关视频）。