m:n relationship - new pk for connecting table

[enfinity]

Hi there,

I have a couple m:n relationships in a database. Now, I was wondering whether

(1) to create a new primary key in the connecting entity or
(2) to use the combination of the 2 FK as new combined PK.

The attached screenshot shows option 1. What option would be better for good query performance?

Thanks!
Steve

 

[Call_Me_Sam]

Hi there,

I have a couple m:n relationships in a database. Now, I was wondering whether

(1) to create a new primary key in the connecting entity or
(2) to use the combination of the 2 FK as new combined PK.

The attached screenshot shows option 1. What option would be better for good query performance?

Thanks!
Steve

Steve,

this is very similar to the question i was asking in this thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=151043

I got some helpful pointer about indexes.

 

[gemma-the-husky]

without looking at the related thread

you can use either, i think, but where you have compound primary keys, it definitley becomes awkward cponnecting tables in access query diagrams for instance, particularly where you have left/right joins

so although strictly its superfluous adding say an extra autonumber field/key, it can make life easier later

 

[Rabbie]

without looking at the related thread

you can use either, i think, but where you have compound primary keys, it definitley becomes awkward cponnecting tables in access query diagrams for instance, particularly where you have left/right joins

so although strictly its superfluous adding say an extra autonumber field/key, it can make life easier later

You can use either. I don't usually bother with the autonumber PK but just use a compound key to prevent duplicates.

It isn't usually a problem connecting tables in queries because you normally would link one of the FKs to the corresponding PK in 1 parent and similarly with the other link. I have never had a scenario when I needed to match both fields to the same place.