许多关系设计 - 交叉表设计 [英] Many to Many Relation Design - Intersection Table Design

问题描述

我想知道多对多关系的交叉表有什么更好的设计。

I'm wondering what a better design is for the intersection table for a many-to-many relationship.

我正在考虑的两种方法是： p>

The two approaches I am considering are:

CREATE TABLE SomeIntersection 
(
     IntersectionId UNIQUEIDENTIFIER PRIMARY KEY,
     TableAId UNIQUEIDENTIFIER REFERENCES TableA NOT NULL,
     TableBId UNIQUEIDENTIFIER REFERENCES TableB NOT NULL,
     CONSTRAINT IX_Intersection UNIQUE(TableAId, TableBId )
) 

或

CREATE TABLE SomeIntersection 
(
     TableAId UNIQUEIDENTIFIER REFERENCES TableA NOT NULL,
     TableBId UNIQUEIDENTIFIER REFERENCES TableB NOT NULL,
     PRIMARY KEY(TableAId, TableBId )
) 

对另一个有利吗？

编辑2：****请注意：我计划使用Entity Framework提供一个API为数据库。考虑到这一点，EF中的一个解决方案是否比其他解决方案运行得更好？

Are there benefits to one over the other?
EDIT 2:****Please Note: I plan to use Entity Framework to provide an API for the database. With that in mind, does one solution work better with EF than the other?

编辑：在相关的注释中，两列参考相同的表格（下面的例子），有没有办法使两个字段在记录上有所不同？

On a related note, for a intersection table that the two columns reference the same table (example below), is there a way to make the two fields differ on a record?

CREATE TABLE SomeIntersection 
(
     ParentRecord INT REFERENCES TableA NOT NULL,
     ChildRecord INT REFERENCES TableA NOT NULL,
     PRIMARY KEY(TableAId, TableBId )
)

我想阻止以下

ParentRecord          ChildRecord
=================================
      1                    1         --Cyclical reference! 

推荐答案

第二个版本是对我最好的，它避免创建额外的索引。

The second version is the best for me, it avoids the creation of an extra index.

这篇关于许多关系设计 - 交叉表设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！