Hello, would greatly appreciate any help with this question.

How do you implement a relationship between two tables where the primary key on one table is referenced twice as foriegn keys in a second table?

An example - forgive any breach of conventions

tblROLES
RoleID, EntityID, RoleType
01, LizHurley, Model
02, m51t, Student

tblASSOCIATIONS
PrimaryAssociate, SecondaryAssociate, Association
01, 02, Stalker

The RoleID attribute is used for both the PrimaryAssociate and SecondaryAssociate fields in the ASSOCIATIONS table. An association is a relationship between two people performing a role. Therefore I think I need to reference the ROLES table twice to describe an association.

How could I logically model this? I tried creating a double relationship in MS Access and it produced a duplicate ROLES table so that there was two single relationships from each ROLES table.

Thanks in advance