By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,763 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Performance with combined key in junction table

P: n/a
I have an application with two tables, A and B. Each has an autonumber
unique ID field, plus other data.

I have a junction table, AB, containing fields AID, BID, and Count (a
number).

AB has a combined unique key comprising AID and BID, that is, any
combination of the two must be unique, even though any given AID or BID may
exist in many records.

I need to do queries across AB from A to B and vice versa.

Would they be faster if AID and BID were separate keys, and I handled
uniqueness constraints myself?

Jet 4.0, OLEDB, C++, Windows.

RDeW
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Fri, 5 Aug 2005 20:14:42 -0700, "Riley DeWiley"
<ri***********@gmail.com> wrote:

Try it, and report back to us.

The PK in the junction table across both fields is the right thing to
do, so leave it.
I'm assuming you already created enforced relationships between the
tables. That creates hidden indexes behind the scenes, so you already
have at least an index on BID. Performance should already be optimum.

With compound indexes (those over more than one field), an additional
index over Field1 doesn't add value: the compound index is already
organized that way. In your case an index on Field2 is already taken
care of by your relationship to table B.

-Tom.
I have an application with two tables, A and B. Each has an autonumber
unique ID field, plus other data.

I have a junction table, AB, containing fields AID, BID, and Count (a
number).

AB has a combined unique key comprising AID and BID, that is, any
combination of the two must be unique, even though any given AID or BID may
exist in many records.

I need to do queries across AB from A to B and vice versa.

Would they be faster if AID and BID were separate keys, and I handled
uniqueness constraints myself?

Jet 4.0, OLEDB, C++, Windows.

RDeW


Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.