Best way to model A-> B, B->C and A->C style relations

Last Post 09 Feb 2007 12:12 PM by JHunter. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
teksg
New Member
New Member

--
09 Feb 2007 11:25 AM
I have database model with entities A, B, C, D, E.
There are direct and indirect dependencies between them that need to be stored.

For example,

A has direct relation with B
i.e A-->B stored in table AB

B has direct relation with C
i.e B->C stored in table BC

A also has direct relation with C i.e A->C
From above, A and C also have indirect relation A->B->C

D and E can also potentially have similar relationships.

Question - Does it make sense to store relations between 2 entities in tables like AB above? Is there better way to do it?
Would the direct and indirect relations add to data integrity and validation issues?

Thanks,
JHunter
New Member
New Member

--
09 Feb 2007 12:12 PM
It sounds like you're describing foreign key relationships (one-to-many/one-to-one):

CREATE TABLE C (
PK_C_ID Int,
CONSTRAINT PK_C PRIMARY KEY CLUSTERED ( PK_C_ID ),
)

CREATE TABLE B (
PK_B_ID Int,
FK_C_ID Int,
CONSTRAINT PK_B PRIMARY KEY CLUSTERED ( PK_B_ID ),
CONSTRAINT FK_B_C FOREIGN KEY ( FK_C_ID ) REFERENCES C ( PK_C_ID )
)

CREATE TABLE A (
PK_A_ID Int,
FK_B_ID Int,
FK_C_ID Int,
CONSTRAINT PK_A PRIMARY KEY CLUSTERED ( PK_A_ID ),
CONSTRAINT FK_A_B FOREIGN KEY ( FK_B_ID ) REFERENCES B ( PK_B_ID ),
CONSTRAINT FK_A_C FOREIGN KEY ( FK_C_ID ) REFERENCES C ( PK_C_ID )
)



Or are you storing many-to-many?


CREATE TABLE A (
PK_A_ID Int,
CONSTRAINT PK_A PRIMARY KEY CLUSTERED ( PK_A_ID )
)

CREATE TABLE B (
PK_B_ID Int,
CONSTRAINT PK_B PRIMARY KEY CLUSTERED ( PK_B_ID )
)

CREATE TABLE AB (
FK_A_ID Int,
FK_B_ID Int,
CONSTRAINT PK_AB PRIMARY KEY CLUSTERED ( FK_A_ID, FK_B_ID ),
CONSTRAINT FK_AB_A FOREIGN KEY ( FK_A_ID ) REFERENCES B ( PK_A_ID ),
CONSTRAINT FK_AB_B FOREIGN KEY ( FK_B_ID ) REFERENCES C ( PK_B_ID )
)

Please excuse any typos :S

Jamie
You are not authorized to post a reply.

Acceptable Use Policy