我的简单的测验数据库，包含主题和子主题，是否被正确设计？

Question

我的简单的测验数据库，包含主题和子主题，是否被正确设计？

3

我正在制作一个简单的测验数据库，其中每个问题都有一个答案和一个或多个图像文件，并属于一个子主题，而该子主题又属于一个主题。此外，每个组可能属于三个级别之一。

我希望展示一个问题（如果存在图像，则还需要展示图像），并将用户的答案与正确答案进行比较。为了显示适当的问题，我需要主题和子主题。

QUESTION
-------------------
question_id      pk
question    varchar
answer      varchar
subtopic_id foreign_key

MEDIA
-------------------
media_id         pk
file_name   varchar
question_id foreign_key

SUBTOPIC
-------------------
subtopic_id      pk
subtopic    varchar
topic_id    foreign_key

TOPIC
-------------------
topic_id         pk
topic       varchar
level       choices(1,2,3)

我的数据库是否设计得当？如果不是，我该如何改进它？

diagram

注：

级别可以拥有独特的主题（主题1和主题3）和共享的主题（主题2）。
一个主题可以有一个或多个子主题，但一个子主题不能属于多个主题。
一个子主题可以有一个或多个问题，但一个问题只能属于一个子主题。两个问题（问题2和问题3）可能拥有相同的子主题和主题，但它们的级别可能不同。主题（主题2）和子主题（子主题2和子主题3）可以属于许多级别（级别1和级别2）。一些子主题仅包含级别1的问题，而另一些子主题则包含级别2的问题（问题4）（子主题3）。
一个问题只能有一个答案，并且该答案不能属于另一个问题。
一个答案（或其问题）可以有一个可选的图像文件，并且该图像文件可以用于其他问题。

- user126284

5

“更好”需要考虑你希望如何使用它的概念。 - Mitch Wheat

3

@Mitch：我不同意。尽可能地，数据库模式应该在没有任何预期使用情况的情况下进行设计。这是关系模型的基本原则。现代数据库之所以糟糕，是因为它们与RM的原始思想产生了根本性的分歧，因此我们经常被迫考虑预期的使用情况。但是，这应该被视为对我们反乌托邦现实的妥协，而不是作为最佳实践来坚持的设计目标。 - Marcelo Cantos

2

你还需要告诉我们索引。 - RibaldEddie

1

@Mitch：此外，我绝对不同意“更好”需要一个预期使用的概念。许多设计可以在绝对意义上变得“更好”。 - Marcelo Cantos

1

@baha 什么使一行数据唯一？ - RibaldEddie

显示剩余4条评论

5个回答

3

由于问题属于子主题，因此问题表应该有一个指向子主题id的外键。

- pinkfloydx33

3

当然，根据你的要求，这是标准化的并且看起来很好。但是，你可能在问题表中遗漏了subtopic_id。

- BobbyShaftoe

2

你应该将id命名为它们所在的表格的名称，例如question_id, media_id等（在它们作为主键时，而不仅仅是在它们被用作外键时）。这样做可以使连接更容易，因为你可以这样说：FROM question JOIN MEDIA USING (question_id)）。
question表缺少subtopic_id。
你可能想要用一个LEVEL表上的外键替换level，将其命名为level_id，但这不是一条硬性规定，只是一个建议。它更加灵活，但也需要更多的工作；最终还是要看你的问题域是什么。

除了这些小错误之外，我认为它看起来很好。

- Marcelo Cantos

0

命名规范建议：

主键：someNameId 外键：someOtherName_Id

目前在一些表中你有 media_id 和 question_id。若不查看数据结构则不清楚每个字段的含义。现在想象一下，你有80张表，每张表有20到40个属性。你会迷失于哪里是外键，哪里是主键。

- user338195

1

我强烈不同意。除一种情况外，PK和FK的列名应始终相同：在子表中，ParentKey具有强烈的含义。唯一的例外是角色发生变化或不明确的情况。组装表中的PartId PK具有两个PartId FK，将分别为AssemblyId和ComponentId。此外，如果您有80个每个表中有20到40个列的表，这当然意味着您在各处都有FK，那么您拥有的是非规范化的数据堆，而不是数据库。在这种情况下，您需要帮助处理所有这些重复的FK，但不是在数据库中。 - PerformanceDBA

1

清晰明了。media_id始终是媒体ID，没有其他含义。如果它在媒体中，则为主键。如果它出现在其他任何地方，则为外键。这有什么不清楚的吗？我不需要看数据模型，但如果以某种逻辑方式绘制，传达意义和顺序，而不是将80个表挤入页面的矩形框架中，那就最好了。 - PerformanceDBA

给我一个包含5个或更多外键的标题表的例子，我会解释我的观点。 - user338195

1

@vikp：你已经表达了你的观点，提供了例子并进行了解释。我只有很少这样的表格，它们是客户的，而不是我的炫耀。它们肯定没有你所暗示的问题。其次，这是否意味着少于5个FK的表格没有你的问题？如果是这样，那就是大多数人的表格。你试图解决一个不存在的问题，只有你才有（由于你的非规范化数据收集）。你真的需要提供你的例子。 - PerformanceDBA

嗯，你正在编写一个左连接的表。连接很可能是在主键和外键之间进行的。当你阅读连接子句时，它将是 Left Join B.CustomerId On C.CustomerId。从这个阅读中，你如何知道哪个表有 CustomerId 作为主键，哪个表有 CustomerId 作为外键呢？好的，简单的例子。很可能 C 表示 Customer 表，但如果你对数据结构不熟悉怎么办？Left Join C.CustomerID on B.Customer_Id 显然表明 B 是一个具有 Customer_Id 作为外键的表。 - user338195

1

@vikp：我很难接受你的逻辑。你试过使用sp_help <table>吗？你看不到FKs vs PKs vs Indices吗？如果你不确定哪个是哪个，或者不熟悉，那么你如何编写连接代码呢？ 让自己熟悉它！ “ID”与“_id”如何指示FK vs PK？请不要回答，我只是指出了奇怪之处。无论如何，这些建议基于更高的原则，因此仍然适用。 - PerformanceDBA

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- PerformanceDBA · Accepted Answer

很好的问题，你在构建应用程序之前就提出这个问题，说明你有能力学习和改进数据建模技能。

你的模型的限制

从简单的意义上来说，这是规范化的：你已经决定了实体是什么；你需要什么关系；你甚至已经决定它们是没有任何识别特征的无意义的东西，并给它们分配了IDENTITY列作为物理键。然后你规范化了表格。

这与关系模型或规范化无关。

这是以应用程序的角度思考的典型方式（可以理解，因为这是你拥有的所有内容，而且你知道功能需求）。但这与数据无关。如果你从这个角度创建它，你将得不到一个数据库，你将得到一个应用程序的存储位置。现在，如果该应用程序只是为一个测验而编写并被丢弃，那是可以接受的。但是，如果测验将在一个月或两个月内运行；如果公司仍将存在十年；如果测验将增加；如果要运行的统计数据将增加、改进和成熟... 那么你将遇到很多问题。每次你增强应用程序时，你都必须替换它的“数据库”。在某个时候，他们会决定应用程序（以及其中包含的“数据库”）太昂贵而丢弃它们。

即使应用程序存在的时间很短，由于数据收集没有被规范化，因此非常有限，商业关系数据库管理系统中实现的“权力”对您而言是失去的。导航（由于SQL连接繁琐）更加繁琐。例如，要从子代到祖父母，您被迫获取父代，即使您不从中获取任何列。用户只能通过应用程序访问数据，它是“封闭”的。现在有成千上万的报告工具连接到数据库，允许用户执行各种查询，这些查询还没有被识别给您，而无需等待应用程序团队实施变更请求：这种能力已经丧失。您将不得不每年或更长时间重新设计数据堆栈。

规范化产生开放式数据库，允许无限制地进行访问。

数据的寿命比应用程序长得多。它存活于应用程序之后，并对组织有价值。当应用程序被替换时，他们将提取数据并希望准备好加载数据的替换应用程序。如果他们没有使用数据建模器，则新的应用程序设计人员没有从旧的应用程序设计人员的错误中学到任何经验教训，整个过程将重复发生。

好的，所以组织预计明年仍将存在。好的，所以您将在生产中运行多个测验。好的，所以您已经聘请了数据建模师来审查您的数据集合，以便您不会犯这些错误。太棒了。谢谢。我接受。

关系建模203

数据建模(a)完全独立于应用程序和任何你可能知道的功能，(b)使用完全不同的技术，应用程序开发人员不知道。所以让我们开始吧。这不是一个关于规范化的正式课程（那需要太长时间，而且理论会让你感到无聊）；这只是在模型师工作时看着他的肩膀。你需要做的就是诚实地回答问题（每次我发布一个问号，请停下来回答，然后再继续），诚实地回答（不是暗示你不诚实；只是强调“我不知道”是可以接受的，因为它确定了我们需要解决的领域；而明确的肯定和否定则使我们避免了讨论）。另外，请原谅我，我会假设答案，只是为了避免其他方面的来回延迟；如果我犯了任何错误，请指出来，我会纠正模型。

好的，你有一些实体是清楚的，所以让我们从它们开始，我认为它们看起来像这样沙滩涂鸦。我故意不使用标准符号，因为在这个阶段我不想将那些含义引入其中，因为我们还没有达到那些含义，并且我们不想向理解标准符号的人（包括我们自己）传递错误的期望。

每一行实体如何与其他行唯一地识别？这很重要，因为它能帮助我们验证该实体是否确实是一个实体，而不是唐老鸭或者虚构的东西。从提供的列中，你至少会有：
Question.Question
.
Media? 我应该如何确保没有42个完全相同的图像？如果允许这种情况，当我们的上司对此发飙时，你会踢我的屁股，然后他也会这么做。IDENTITY对我没用，它会愉快地让我插入重复项。FileName是一个好的标识符。 Media.FileName
.
Topic? 你想要101个完全相同的Topic吗？我想不会。
Topic.Topic
.
Subtopic.Subtopic？不行。IDENTITY？不行。您非常清楚它属于哪个Topic，而我已经将Topic作为外键，它有意义；如果它是一个依赖子，而不是独立的孤儿，则FK是一个识别关系：Subtopic: (Topic、Subtopic).
.
好了，我们已经准备好了。
实体之间如何相关？
你清楚Subtopic是Topic的子类。好的。
.
问题是Subtopic的子类吗？我认为不是。你真的想要"一个家庭拥有多少辆汽车"这个问题在42个测验中都存在，插入42行吗？ "永远不会有相同的问题出现在两个测验中"是可疑的，所以请不要这么说，除非你打算写下来。更糟糕的是，让我们假设这个问题是一个非常生动的问题，那么我们也将有42个Media条目。如果老板因为在42个地方改变相同的问题而感到不满意，当他这样做时，他发现一些图像是古老的，它们没有在去年进行的主要图像更新时更新...那就是您的问题了。
.
我们允许一个问题存在于多个测试中如何？
.
所以问题是独立的。
回到实体。在我们的讨论中，从第五个词开始，你不断地说"Quiz"，我也这么说，但我们没有一个Entity。我无法想象一个松散和重复的问题集，在我刚刚去掉了重复项之后，没有确认所述问题集的存在。现在你可能会说"实际上测验是Topic"，但是这很限制：每次老板需要为一些新客户添加测验时，他都必须重新添加整个Topic/Subtopic/Question集合，即使他知道这些确切的问题以前已经在现有的测验中输入过；这就是他赢得新业务的原因，也是他终于要盈利的原因，而你刚刚将其降至零。上次他不高兴的时候我仍然疼，所以让我们确保他没有另一个借口。这使他能够独立地增长、培育和改变测验和Topic/Subtopics，而不会重复问题：
实体。Quiz
键？好吧，它最好有一个索引，以确保我们不提供重复的测验给他。假设我们不想将CHAR(80)键带入子级，
完成了，就这样。希望我已经清楚地传达了练习是反复的，这就是为什么它被称为建模。宇航员有单独的、私人的住处，并且他们可以相互交流。

------------------------------------------------------------------------------------------

那是什么？你也想要数据模型吗？好的，给我五分钟。请自己拿杯咖啡。

------------------------------------------------------------------------------------------

它很小，我可以给您物理数据模型。我使用IDEF1X方法来建模关系数据库，这是一种标准，因此数据模型呈现为IDEF1X符号，如果您需要快速回顾符号的含义，请查看该链接。
1. 多对多关系是一个逻辑概念，在逻辑模型中如此绘制。在物理模型中，它们作为联结表实现。我已经提供了它们。规范化的数据库有更多的表（无需担心），但每个表的列较少，没有重复的列（没有更新异常）。
2. 我的天啊。Topic和SubTopic列非常大！我们无法将那些巨大的外键迁移到Question中。[与业务用户讨论。] 好的，他们说只会有一百个主题和几千个子主题。不需要NUMERIC（10,0）。他们希望下拉菜单中显示完整的Topic和Subtopic，并且同意它必须是唯一的，但另外一个短的CHAR（6）代码也很好。
看，它确实来回移动。纸是便宜的；与任何人讨论；改进、纠正、更改、调节、改善，而不创建单个表或编写一行代码；最终你将拥有一个值得编写代码的模型。其他任何东西都不是。学习的唯一方法是呈现出实物，并使其被拒绝或更改；在纸上犯所有错误，而不是在数据库中。
请注意，Surrogate键始终是附加键，附加索引。它们永远不会替代主键（这就是你所拥有的内容，以及Eddie试图让你考虑的内容：你没有防止重复项，你只是拥有一个无意义的键来保证行是唯一的，就像电子表格一样；和一种虚假的安全感）。因此，我们需要将它们最小化，而不是在每个表上随意打上它们。
我希望我已经表明，像“永远不要使用替代品”和“总是使用替代品”这样的简单规则太愚蠢了，无法讨论。不，仔细的建模意味着：理解并考虑它们是附加的，而不是替代真正的键。只有在必须使用时才使用，而且必须使用它们。在这里，我只使用了一个IDENTITY。对于用户有意义的三个短代码是代理，但它们具有含义；IDENTITY列没有（它们最终具有意义，但它们无法支持，这是问题的一部分）。
我会将DataTypes留给你处理。但请记住，varchars和Nullable列会强制使列变量。如果在索引中使用会非常慢（每个条目在每次访问时都必须进行一些“解包”，即使是中间级别），因此必须避免使用，除非你想提供一个缓慢的数据库。
同样，如果您不希望在每次更新时将页面上的行移动，那么应该使用固定长度的列。这意味着我们不能懒惰地使用varchar。
现在，我们有了一个容纳火箭燃料的舱室。
回复评论1
从您的最后数据模型来看，如果我在问题表中省略topicCode，是否更好？在子主题和问题表中都包含topicCode是否感觉有些冗余？
很好的问题。
（第五个是数据模型；第四个是实体关系图；前三个是空想，努力实现。）
1. 在子表中将主键迁移为外键不是冗余的，而是必须的。
2. Subtopic PK 是 (TopicCode, SubtopicCode)，是一个复合键（商业数据库支持的关系模型要求）。在 Subtopic 的水平线以上。
  - 一些开发者害怕使用复合键，因为需要在 WHERE 子句中引用多个字段（纯粹的懒惰；SQL 在连接方面很繁琐；请应对这种情况）。
    .
3. 这是因为 Topic::Subtopic 关系是标识关系，这意味着父表的主键用于构造子表的主键，形成一个复合键。请注意，父表的主键必须在子表中作为外键存在，因此它不是冗余的，而是必须的。这已经被证明可以显著增加数据库的“功率”或“关联性”，并且大大增加了易用性（强大的用户通常比开发人员更擅长使用关系型数据库）。
  - 这就是为什么它被确定为标准：IDEF1X 是一种强制执行关系模型更严格应用的标准；它促进了对所有键的深思熟虑，这当然对于假定的关系型数据库的“关联性”至关重要。
    .
4. 在问题中，因此与 Subtopic 相关的 FK 是 (TopicCode, SubtopicCode)。
5. 如果在 Topic 和 SubTopic 中使用 ID，则 Question 将具有 (SubtopicId) 作为与 Subtopic 相关的 FK，并且您将失去导航功能和含义。
  - 使用标识父项（和祖父项）的复合键比使用 ID 更优越的一个原因是，例如，当您仅需要从 Question 和 Topic 获取列时，您不必从 Subtopic 进行选择；而对于 ID，则被迫从 Subtopic 进行选择，这不是报告要求的一部分，而是由于较差的关系建模而强加的；未能实现标识关系，即强大的含义。这只是数据堆仅具有 ID 作为 PK（作为 FK 承载）执行比规范化关系型数据库更多工作的原因之一。
    - 由于涉及的表格简单且小，这可能不是展示关系键优越性/IDENTITY 列限制的好例子；请考虑任何大型的 Grandparent::Parent::Child 表格。
    - 展示这种优越性/限制的更好的例子，并值得任何希望进一步讨论此问题的人考虑的是我的高级课程数据模型，因为这个问题被明确地解决了。
  - 另一个原因是失去了含义。例如，用户可以简单地选择 TopicCode、SubtopicCode、Question FROM Question，并理解结果集。
回应评论2

进阶数据模型101102

回应评论3

我不明白如何使用同一张图片文件来回答不同的问题？

简单的例子，"列举一种不能飞的鸟"和"什么是鸸鹋"两个问题都可以使用同一张鸸鹋的照片。这是根据上面主贴中的(1)所假设的。该模型通过在FileName上提供非唯一索引来实现。

是否应该在Question表中包含Media.FileName外键并删除Media表中的QuestionId？

好吧，不需要。我建模的方式有几个优点。如果它在Question表中，那么很大一部分时间它将为Null。你需要在Filename上建立一个索引来搜索它们；看看它是否被使用；或者没有；等等。这意味着我们不能对FileName列进行索引（或者我们可以对其进行索引，但由于Nulls，索引会很慢）。现在它的建模方式如下：
- 一个问题的"选项"或子类型（具有图像的问题）
- 没有Nulls
- 快速的非唯一索引
它可以被设置为一个图像库（唯一索引）。我们知道一个问题可以有零到一个图片。您可以告诉我们您的决定，我会更改模型：
- 图片文件名唯一；每个问题仅能使用一次
- 图片文件名唯一；可以在多个问题中使用
这是什么样的关系？1::1？

是的。媒体端是“可选的”。具体来说：一个问题有零个或一个媒体。因此，“可能有”是关系的动词短语或标题。

模型考虑了自然层次结构：父项位于子项之上；子类型处于同一水平线上。

看起来不像其他n :: n或1 :: n关系。

没有n :: n关系。（逻辑层面上有一个，但在物理层面上被实现为联接表。）

**为什么在QuizQuestion表中包含答案字段？*

你想把用户的答案存储在哪里？

你需要Quiz和QuizQuestion表；它们在之前就是有效实体，你当时没有看到，但现在你看到了。

主题和子主题之间的关系在Subtopic表中定义。在Question表中，您已经包括了TopicCode和SubtopicCode。您说（如果我没有错的话），包括TopicCode很好，因为我可以直接从Question表中获取Topic而不必加入Subtopic表。

是的。根据上述（1.子主题）。

但是，根据您的模型，每次我向数据库输入新问题时，都应该为问题选择一个主题和子主题[从现有列表中]。

好的，无论如何你都必须这样做；模型只是强制执行它。我通过数据了解到这就是你所需要的。一个子主题可以属于多个主题。因此你需要给它同时提供主题和子主题（特定的组合）。

那么这个数据库怎样保证子主题属于在子主题表中描述的某个主题呢？

你没看见吗？你能看到子主题表已经提供了具体的 [你插入的任何东西] 主题::子主题组合……而不是其他的吗？那么，当你添加一个新问题并给它一个主题和子主题（这是 SubTopic 的主键，也是 Question 的外键）时，数据库将强制执行 FK，以便只使用存在于 SubTopic 中的 Topic::Subtopic 组合之一。

这是对自然良好关系键的一点小见识。

这就是一个好的关系模型的美妙之处：它从一个单一的[正确建模的]结构中提供了许多要求。

回应评论4

进阶数据模型101103

进阶数据模型101109

IDEF1X符号表示法