mysql - 这是好的数据库规范化吗？

Question

我是使用 mysql 的初学者，我正在尝试学习最佳实践。我已经设置了一个类似的结构，如下所示。

(包含所有唯一条目的主表)TABLE = 'main_content'

+------------+---------------+------------------------------+-----------+
| content_id |  (deleted)    | title                        | member_id | 
+------------+---------------+------------------------------+-----------+
|          6 |               | This is a very spe?cal t|_st |      1    |
+------------+---------------+------------------------------+-----------+ 

(提供每个难度的总和并加入 id --> 实际名称)TABLE = 'difficulty'

+---------------+-------------------+------------------+
| difficulty_id | difficulty_name   | difficulty_total |
+---------------+-------------------+------------------+
|             1 | Absolute Beginner |                1 |
|             2 | Beginner          |                1 | 
|             3 | Intermediate      |                0 |
|             4 | Advanced          |                0 |
|             5 | Expert            |                0 |
+---------------+-------------------+------------------+

(此表确保可以为每个条目插入多个值。例如， 此特定条目表明提交有 2 个困难)TABLE = 'lookup_difficulty'

+------------+---------------+
| content_id | difficulty_id |
+------------+---------------+  
|          6 |             1 |
|          6 |             2 |
+------------+---------------+

我将所有这些加入到一个可读的查询中:

SELECT group_concat(difficulty.difficulty_name) as difficulty, member.member_name
FROM main_content
INNER JOIN difficulty ON difficulty.difficulty_id 
IN (SELECT difficulty_id FROM main_content, lookup_difficulty WHERE lookup_difficulty.content_id = main_content.content_id )
INNER JOIN member ON member.member_id = main_content.member_id

以上工作正常，但我想知道这是否是好的做法。我实际上遵循了布局的结构 Wikipedia's Database Normalization example .

当我使用 EXPLAIN 运行上述查询时，它说:'Using where;使用 join buffer' 并且我正在使用 2 DEPENDENT SUBQUERY (s) 。我看不出有什么方法可以不使用子查询来达到同样的效果，但话又说回来，我是一个菜鸟，所以也许有更好的方法....

Answer 1

数据库设计看起来不错 - 关于您的查询，您可以专门使用连接重写它，例如:

SELECT group_concat(difficulty.difficulty_name) as difficulty, member.member_name
      FROM main_content
        INNER JOIN lookup_difficulty ON main_content.id = lookup_difficulty.content_id
        INNER JOIN difficulty ON difficulty.id = lookup_difficulty.difficulty_id
        INNER JOIN member ON member.member_id = main_content.member_id