Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
458 views
in Technique[技术] by (71.8m points)

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

or

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

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?

EDIT: 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 )
)

I want to prevent the following

ParentRecord          ChildRecord
=================================
      1                    1         --Cyclical reference! 
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

It's a topic of some debate. I prefer the first form because I consider it better to be able to look up the mapping row by a single value, and like to enforce a perhaps-foolish consistency of having a single-column primary key available in every table without fail. Others think that having that column is a silly waste of space.

We meet for a few rounds of bare-knuckle boxing at a bar in New Jersey every couple of months.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...