有多对多关联的连接表有选项吗？ [英] Are there any options to a join table for many-to-many associations?

问题描述

假设我有两个数据表，这些数据不应该通过使用我的应用程序（即域表）来改变，例如汽车的模型和颜色的一辆车。

型号表列出了存在的不同类型的汽车型号。 p>

颜色表列出了汽车可以进入的不同类型的颜色。

现在，这可能不是很明显，但两者之间存在着一种关系。 颜色可能仅适用于特定型号，或更可能是同一品牌的几种型号。 模型当然不是特定的颜色，但它确实带有我想要存储在数据库中的颜色的选择或选择。

因此，它们之间存在多对多的关系，这表明我应该将该关系的细节存储在连接表中。所以，如果你原谅我自己的符号，它会看起来像这样：

 沃尔沃V70<  - >珍珠白
沃尔沃V70  - 翡翠绿色
沃尔沃V70  - 夜黑
沃尔沃V70  - 鲑鱼粉
沃尔沃V70  - 海洋蓝色
沃尔沃V70  - 覆盆子红
 
沃尔沃V60  - 珍珠白
沃尔沃V60  - 翡翠绿色
沃尔沃V60  - 夜黑色
沃尔沃V60  - 三文鱼粉红色
沃尔沃V60  - 海洋蓝色
沃尔沃V60  - 覆盆子红
 
 ... 
  

这是一堆重复的文字。更容易的是，如果我可以做

  [Volvo V70，V60，S60，S40] [珍珠白，翡翠绿，夜黑，三文鱼粉红，海洋蓝，树莓红] 
  

并移动

常规连接表是否有任何选项可以以任何方式简化此过程？

解决方案

• 如果模型不共享颜色组，则设计将是一个表：

   模型[模型]有颜色[颜色] 
    




• 如果模型共享颜色组，则有两个表：

    model [model]来在组[组]的颜色
  组[组]有颜色[颜色] 
    

  这些表连接到第一个表的投影：

    SELECT模型，颜色FROM model_group NATURAL JOIN group_color 
    




• 如果一个模型除了一个组之外还可以具有异常可用和/或不可用的颜色，那么就有异常表。表的组现在是其默认颜色（如果有）：

    model [model]有默认颜色组[组] 
  组[组]有颜色[颜色] 
  模型[模型]是特别可用的颜色[颜色] 
  模型[模型]是非常不可用的颜色[颜色] 
    

  然后，异常表分别与JOIN-plus-PROJECT / SELECT中的UNIONed和MINUSed / EXCEPT给出第一个表：

    SELECT组，颜色FROM model_default NATURAL JOIN group_colour 
   EXCEPT SELECT * FROM model_unavailable 
   UNION SELECT * FROM model_available 
    

冗余不是关于多个地方出现的值。每个表（和查询表达式）都有一个关联的填写（ - ）命名空白语句模板（又名谓词）。在表中创建一个真实语句的行。如果你有两个独立的谓词，那么你需要两个表。相关的值在每一行的行中。

重新制作有关该应用的声明，请参阅此。 （并搜索我的其他答案，一个表的声明或标准。）归一化有助于它替换表的行，其中表示... AND ...形式的其他表，表示... 分开。请参阅此和此。

如果您共享组，并且仅对模型和颜色使用单列双列表，则其谓词为：

 对于某些组
模型[模型]来自组[组]的颜色
 AND组[组]具有颜色[颜色] 
  

所以第二个项目符号从这个谓词中删除一个AND，即多值依赖 。否则，如果您更改模型的组或组的颜色，则必须同时始终更改多行。 （重点是减少冗余中的错误和复杂性，而不是节省空间。）

如果不想重复字符串实现（-dependent）原因（占用空间或以更多连接为代价的操作速度），然后添加名称ids和字符串表，并用id列和值替换旧名称列和值。 （这不是正常化，这是为了实现依赖的数据优化权衡而使您的模式复杂化，而您应该演示这是必需和有用的。）

Let's say I have two tables with data that isn't supposed to change through the use of my application (i.e. domain tables) for something like the model of a car and the color of a car.

The models table lists different types of car models that exist.

The colors table lists different types of colors that a car can come in.

Now, it might not be evident right away, but there exists a relationship between the two. A color may only be available for a certain model, or more likely several models of the same make. A model certainly doesn't come in a specific color, but it does come with a selection, or choice, of colors that I do want to store in the database.

So there exists a many-to-many relationship between them, which suggests that I should store the details of that relationship in a join table. So, if you forgive my own notation, it would look something like this:

Volvo V70 <-> Pearl White
Volvo V70 <-> Emerald Green
Volvo V70 <-> Night Black
Volvo V70 <-> Salmon Pink
Volvo V70 <-> Ocean Blue
Volvo V70 <-> Raspberry Red

Volvo V60 <-> Pearl White
Volvo V60 <-> Emerald Green
Volvo V60 <-> Night Black
Volvo V60 <-> Salmon Pink
Volvo V60 <-> Ocean Blue
Volvo V60 <-> Raspberry Red

...

This is A LOT of repeated text. Easier would be if I could just do

[Volvo V70, V60, S60, S40] <-> [Pearl White, Emerald Green, Night Black, Salmon Pink, Ocean Blue, Raspberry Red]

and move on to the next car model and set of colors.

Are there any options to a regular join table that can simplify this process in any way?

解决方案

• If models don't share colour groups then the design would be one table:

  model [model] comes in color [color]
  

• If models share colour groups then have two tables:

  model [model] comes in the colors of group [group]
  group [group] has color [color]
  

  These tables join with projection to the first table:

  SELECT model, color FROM model_group NATURAL JOIN group_color
  

• If a model can have exceptional available and/or unavailable colours in addition to or instead of a group then have exception tables. A table's group is now its default colours (if any):

  model [model] has default color group [group]
  group [group] has color [color]
  model [model] is exceptionally available in color [color]
  model [model] is exceptionally unavailable in color [color]
  

  The exception tables are then respectively UNIONed with and MINUSed/EXCEPTed from a JOIN-plus-PROJECT/SELECT to give the first table:

  SELECT group, color FROM model_default NATURAL JOIN group_colour
  EXCEPT SELECT * FROM model_unavailable
  UNION SELECT * FROM model_available
  

"Redundancy" is not about values appearing in multiple places. It is about multiple rows stating the same thing about the application.

Every table (and query expression) has an associated fill-in-the-(named-)blanks statement template (aka predicate). The rows that make a true statement go in the table. If you have two independent predicates then you need two tables. The relevant values go in the rows of each one.

Re rows making statements about the application see this. (And search my other answers re a table's "statement" or "criterion".) Normalization helps because it replaces tables whose rows state things of the form "... AND ..." by other tables that state the "..." separately. See this and this.

If you share groups and only use a single two-column table for model and color then its predicate is:

FOR SOME group
    model [model] comes in the colors of group [group]
AND group [group] has color [color]

So the second bullet removes a single "AND" from this predicate, ie the source of a "multivalued dependency". Otherwise if you change a model's group or a group's colours then you have to simultaneously consistently change multiple rows. (The point is to reduce errors and complexity from redundancy, not save space.)

If you don't want to repeat the strings for implementation(-dependent) reasons (space taken or speed of operations at the expense of more joins) then add a table of name ids and strings and replace your old name columns and values by id columns and values. (That's not normalization, that's complicating your schema for the sake of implementation-dependent data optimization tradeoffs. And you should demonstrate this is needed and works.)

这篇关于有多对多关联的连接表有选项吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！