链接表需要一个无意义的主键字段吗？ [英] Do link tables need a meaningless primary key field?

问题描述

我正在研究几个链接表，我想到（危险的罗宾逊危险），链表的可能结构是什么，以及他们的亲和关系是什么。

我提出了链接表的一些可能的限制：

传统3列模型

• id - 自动编号PRIMARY


• table1fk - 外键


• table2fk - 外键

在大多数书中，这是一个经典的，Nuff说。

索引3列模型

• id - 自动编号PRIMARY


• table1fk - 外键 INDEX（'table1fk'）


• table2fk - 外键 INDEX（'table2fk'） li>
  

根据我自己的经验，您在查询中的字段在传统模型中未编入索引。我发现索引外键字段确实提高了预期的性能。

复合键2列添加主键（'table1fk'，'table2fk'）

• table1fk - 外键


• table2fk - 外键

使用这个我使用一个复合键，以便table1中的记录只能链接到table2上的一个记录。因为密钥是复合的，我可以添加记录（1,1），（1,2），（2,2），没有任何重复错误。

任何潜在的问题复合键2列选项？是否有可能导致的索引问题？表演命中任何可以取消资格的可能选项？

解决方案

对于真实的链接表，它们通常不存在作为我的对象实体对象模型。因此，替代密钥不会被使用。从集合中移除项目导致从两个外键已知的链接关系中删除项目（ Person.Siblings.Remove（Sibling）或 Person.RemoveSibling（Sibling）它在数据访问层被适当地转换为 usp_Person_RemoveSibling（PersonID，SiblingID））。 p>

正如Mike所提到的，如果它成为对象模型中的实体，那么它可能值得一个ID。然而，即使加上时间因素，如关系的有效开始和结束日期以及类似的事情，并不总是清楚的。例如，集合可能具有在合计级别相关联的有效日期，因此关系本身可能仍然不会成为具有任何暴露属性的实体。

我想要补充一点，你可能非常需要在两个外键列上双向索引的表。

I am working on a couple of link tables and I got to thinking (Danger Will Robinson, Danger) what are the possible structures of a link table and what are their pro's and con's.

I came up with a few possible strictures for the link table:

Traditional 3 column model

• id - auto-numbered PRIMARY
• table1fk - foreign key
• table2fk - foreign key

It's a classic, in most of the books, 'nuff said.

Indexed 3 column model

• id - auto-numbered PRIMARY
• table1fk - foreign key INDEX ('table1fk')
• table2fk - foreign key INDEX ('table2fk')

In my own experience, the fields that you are querying against are not indexed in the traditional model. I have found that indexing the foreign key fields does improve performance as would be expected. Not a major change but a nice optimizing tweak.

Composite key 2 columns ADD PRIMARY KEY ('table1fk' , 'table2fk')

• table1fk - foreign key
• table2fk - foreign key

With this I use a composite key so that a record from table1 can only be linked to a record on table2 once. Because the key is composite I can add records (1,1), (1,2), (2,2) without any duplication errors.

Any potential problems with the composite key 2 columns option? Is there an indexing issue that this might cause? A performance hit? Anything that would disqualify this as a possible option?

解决方案

For true link tables, they typically do not exist as object entities in my object models. Thus the surrogate key is not ever used. The removable of an item from a collection results in a removal of an item from a link relationship where both foreign keys are known (Person.Siblings.Remove(Sibling) or Person.RemoveSibling(Sibling) which is appropriately translated at the data access layer as usp_Person_RemoveSibling(PersonID, SiblingID)).

As Mike mentioned, if it does become an actual entity in your object model, then it may merit an ID. However, even with addition of temporal factors like effective start and end dates of the relationship and things like that, it's not always clear. For instance, the collection may have an effective date associated at the aggregate level, so the relationship itself may still not become an entity with any exposed properties.

I'd like to add that you might very well need the table indexed both ways on the two foreign key columns.

这篇关于链接表需要一个无意义的主键字段吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！