连接表的好处？(Benefits of a join table? [closed])

连接表的好处？(Benefits of a join table? [closed])

我试图向同事解释联接表的好处，下面是一个解释。 我对么？

目前，他有一张pic和带有两张桌子的标签之间的关系。 pic表和标签表。 pic表有一个tag_id这是标签表中条目的FK。 这是我的回答：

首先让我们看看pics和tags表。 因此，在您当前的架构中，可以想象两个图片（a＆b）。 我们使用标签#wtf标记图片a和b。 我们现在在out tags表中有两个条目：

pic_id title ------ ----- a wtf b wtf

你看到了这个问题吗？ 因此，假设我们在1000个不同的照片上有1000 wtf标签。 使用相同的架构，我们现在有一个膨胀的标签表，其中包含所有这些重复数据（以及浪费的空间）。 当我们有多对多关系时就会出现这个问题。 在这种情况下，许多照片可以有许多标签，许多标签可以有许多照片。 我们如何解决这个问题？ 答案是连接表。 所以我们创建一个新表。 让我们称之为pic_tag 。 该表将包含pic_id和tag_id列。 所以现在新表看起来像：

pic_tag

pic_id tag_id ------ ------ a 1 b 1

标签

id name -- ---- 1 wtf

PIC

id name -- ---- a pic1 b pic2

所以这对我们来说有几件事情。 首先，它节省了空间。 我们只存储字符串'wtf'一次。 其次，要查找标签为'wtf'的所有图片，我们首先转到标签表并找到'wtf'的id，然后转到pic_tag表并搜索该id，这比搜索膨胀的'标签更有效率'给定文字的表格。 换句话说，搜索int比搜索文本快得多。

I am trying to explain the benefits of a join table to a colleague, and below is an explanation. Am I correct?

Currently he has the relationship between a pic and tag with two tables. A pic table and a tag table. The pic table has a tag_id this is a FK to an entry in the tag table. Here was my response:

First lets look at pics and tags tables. So in your current architecture lets imagine two pics (a & b). We tag pics a & b with the hashtag #wtf. We now have two entries in out tags table:

pic_id title ------ ----- a wtf b wtf

Do you see the issue? So imagine we have 1000 wtf tags on a 1000 different pics. With this same architecture we now have a bloated tags table with all this repeated data (and wasted space). This issue arises when we have a many to many relationship. In this case many pics can have many tags, and many tags can have many pics. How would we fix this? The answer is a join table. So we create a new table. Lets call it pic_tag. This table would have columns pic_id & tag_id. So now new tables would look like:

pic_tag

pic_id tag_id ------ ------ a 1 b 1

tags

id name -- ---- 1 wtf

pic

id name -- ---- a pic1 b pic2

So this does a couple of things for us. First it saves space. We only store the string 'wtf' one time. Second, to find all pics with the tag 'wtf' we first go to the tag table and find the id of 'wtf', then go to the pic_tag table and search for that id which is much more efficient that searching a bloated 'tags' table for a given text. Said another way, search for ints is much faster than searching for text.

最满意答案

主要好处是存在只能使用连接表建模的关系。

假设你有两个实体A和B.如果关系A：B是1：1，那么这两个实体可以用一个表来表示。 如果A：B是1：N（比如1个客户可以有N个订单，但每个订单只来自一个客户），那么您可以将其建模为从订单表到客户表的外键。 但是如果A：B是N：M（你的标签场景就是一个很好的例子），你需要一种方法来为每张图片代表0,1或N个标签。 除了连接表之外，没有其他声音关系表示。

请注意，您可以在每个图片中表示多个标记，同时在单个列中打破关系设计的一些原则（如将多个标记或FK存储到标记）。 是否这样做是一个设计决定。

其他好处：您可以改变主意，无论关系是0：1,0：N，1：（0-5），1：N等，而无需更改核心数据模型 - 在触发器或应用程序逻辑中寻址该逻辑。 您可以创建其他索引来帮助进行联接。 您可以引入更多唯一性约束来强制执行数据逻辑等。

但主要的好处是，连接表是建立某些类型关系的唯一关系良好的方式。

The main benefit is that there are relationships that can only be modeled with a join table.

Say you have two entities A and B. If the relationship A:B is 1:1, the two can be represented by a single table. If A:B is 1:N (like 1 customer can have N orders but each order comes from only one customer), then you could model this as a foreign key from orders table to customers table. But if A:B is N:M (your tags scenario is a good example) you need a way to represent 0, 1 or N tags for each picture. There is no sound relational representation for that other than a join table.

Note you could represent multiple tags per picture while breaking some principles of relational design (like storing multiple tags or FK's to tags) in a single column. Whether to do that or not is a design decision.

Other benefits: you can change your mind whether the relationship is 0:1, 0:N, 1:(0-5), 1:N etc. without changing the core data model -- addressing that logic in triggers or application logic. You can create additional indexes to help with joins. You can introduce more uniqueness constraints to enforce data logic etc.

But the main benefit is, join tables are the only relationally sound way to model certain types of relationships.