为什么多对多数据结构需要两个附加表？ [英] Why does many-to-many data structure require two additional tables?

问题描述

此问题基于主题。

如果我们有一对多的数据结构，我们需要一个帮助表来存储一个人的phonenumbers。许多人不能拥有相同的电话号码。

我期待一个解释，为什么我们需要两个帮助表之间的多对多关系。这样的例子是许多用户可以添加相同标签的问题网站：

alt text http://files.getdropbox.com/u/175564/db/db-55.png

为什么我们需要有 Question-Tag-xref 和 code>

为什么我们只能为标签设置一个表格，如下所示：

  Question_id |标记
 1 C 
 1 C ++ 
 2 Java 
 2 C 
  

$ b b

为什么是一个事实，即两个不同的问题有相同的标签的电脑？

解决方案

这只是一个额外表。

这是因为同一个问题可能有很多标签。

和因为相同的标记可以通过许多不同的问题可以使用。

您需要在某处存储（questionId，TAGID），并确保有没有那个副本。

---

我没有关注你对这个主题的问题，但看起来这里有一些不好的设计。我以为你只有一张额外的桌子，因为我认为你有一个明智的结构。

为什么问题标签同时具有标签字符串和标签ID？这对我来说没有什么意义。

---

我不想回过问题的顺序。不过，我想尝试说明我在说什么。所以我使用诺马工具：

这生成以下ER图：

请注意，该额外表所有我们需要保留的标签，只是因为没有关于标签的额外信息。此外，因为标签名称已经是唯一的，所以不需要将作为外键的标签id存储到标签表。如果我们保存有关标记的附加数据，那么可能会有一个单独的标记表，主键仍然是标记名称。如果它成为一个性能问题，那么可以更改为使用整数ID，在这种情况下，标记名称仍然会获得一个唯一的索引。

This question is based on the thread.

If we have one-to-many data structure, we need to have a "help-table" to store for instance phonenumbers for one person. Many person cannot have the same phonenumbers.

I look forward for an explanation why we then need two "help-tables" between many-to-many relations. An example of this is a question-site where many users can add the same tags:

alt text http://files.getdropbox.com/u/175564/db/db-55.png

Why do we need to have the tables Question-Tag-xref and Question-Tags?

Why cannot we just have one table for tags as follows?

   Question_id   |    tag
   1                  C 
   1                  C++
   2                  Java
   2                  C

Why is the fact that two different questions have the same tag a problem for a computer?

解决方案

That's only one "extra" table.

It's because the same question may have many tags.

And because the same tag may be used by many questions.

You need somewhere to store (questionId, tagId) and to make sure there are no duplicates of that.

---

I haven't been following your questions on this topic, but it looks like there's some bad design here. I thought you only had one extra table because I assumed you had a sensible structure. You do not.

Why does Question-Tags have both the tag string and a tag id? That doesn't make much sense to me.

---

I don't want to go back through the sequence of questions. Still, I wanted to try to illustrate what I was talking about. So I created a very simple Object-Role Modeling model of this part of StackOverflow using the NORMA tool:

This generated the following ER diagram:

Note that the "extra" table is all we need to keep for tags, simply because there is no additional information kept about tags. Also, there is no need to store a tag id that is the foreign key to a Tags table, since the tag name is already unique. If we kept additional data about a tag then there would probably be a separate Tags table, with the primary key still being the tag name. That could be changed to use an integer id if it became a performance issue, in which case the tag name would still get a unique index over it.

这篇关于为什么多对多数据结构需要两个附加表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！