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

问题描述

我正在研究几个链接表，我开始思考(Danger Will Robinson，Danger)链接表的可能结构是什么，它们的优缺点是什么.

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:

• id - 自动编号的 PRIMARY
• table1fk - 外键
• table2fk - 外键

这是经典，在大多数书中，'nuff 说.

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

• id - 自动编号的 PRIMARY
• table1fk - 外键 INDEX ('table1fk')
• table2fk - 外键 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.

• table1fk - 外键
• table2fk - 外键

通过这个，我使用了一个复合键，这样 table1 中的记录只能链接到 table2 上的记录一次.因为键是复合的，所以我可以添加记录 (1,1)、(1,2)、(2,2) 而不会出现任何重复错误.

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.

复合键 2 列选项有任何潜在问题吗?是否存在这可能导致的索引问题?业绩大跌?有什么可以取消它作为可能选项的资格吗?

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?

推荐答案

对于真正的链接表，它们在我的对象模型中通常不作为对象实体存在.因此，代理键从未被使用过.从集合中移除项目会导致从两个外键已知的链接关系中移除项目(Person.Siblings.Remove(Sibling) 或 Person.RemoveSibling(Sibling)) 在数据访问层被适当地翻译为 usp_Person_RemoveSibling(PersonID, SiblingID)).

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)).

正如 Mike 所提到的，如果它确实成为您对象模型中的一个实际实体，那么它可能需要一个 ID.然而，即使添加了时间因素，例如关系的有效开始和结束日期等，也并不总是很清楚.例如，集合可能具有在聚合级别关联的生效日期，因此关系本身可能仍然不会成为具有任何公开属性的实体.

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屋！