多对多关系设计 - 交叉表设计 [英] 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.

我正在考虑的两种方法是:

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:****请注意:我计划使用实体框架为数据库提供 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屋！