Setting Indexes on a Junction (associative) Table.

[Kostas]

Hello,

I am having a hard time finding documentation for this online.

1.Say I have a junction table to define a M:N (many to many relationship).
2. The junction table has two fields that are primary keys in the tables
that have the many to many relationship (these fields are the foreign keys
to the junction table).
3. Those two keys form a composite primary key for the junction table.
4. Those keys are indexed (index yes no duplicates) since they are primary
keys in their parent tables.

My question is twofold:
1) Should those fields be indexed explicitly again in the junction table in
access?
2) Speaking outside the framework of access and in the more general context
of database theory, what is the preffered methodology on such composite
keys?
a. Create a combined index on the composite key?
b. Create two separate indexes for each attribute of the composite key?

Many thanks for any replies, this one has got me searching for some hours
now,

Kostas

 

[Brendan Reynolds]

Don't add separate indexes to the two fields, because Access creates hidden
indexes anyway when you define referential integrity between the tables.
Your additional indexes would just duplicate these hidden indexes.

Create a composite unique index including both fields because the same
many-to-many relationship between two records should not exist more than
once.

The classic example used to illustrate this situation is books and authors.
An author can be the author of more than one book, and a book can have more
than one author. So you have an author table, with (for the sake of example)
an AuthorID primary key, and a book table with (again, for the sake of
example) a BookID primary key, and a junction table including AuthorID and
BookID, with a unique index including both fields, because the same author
should not be entered more than once as the author of the same book. There
is no need to add additional indexes on AuthorID and BookID in the junction
table, because hidden indexes will be created on those fields when you
establish referential integrity between the three tables.

 

[Kostas]

Thank you so very much Brendan,
I really appreciate it.

Kostas

 

[John Vinson]

Hello,

I am having a hard time finding documentation for this online.

1.Say I have a junction table to define a M:N (many to many relationship). ok...
2. The junction table has two fields that are primary keys in the tables
that have the many to many relationship (these fields are the foreign keys
to the junction table). ok...
3. Those two keys form a composite primary key for the junction table. ok...
4. Those keys are indexed (index yes no duplicates) since they are primary
keys in their parent tables.

NOT ok. They should be indexed Yes, No Duplicates IN THEIR PARENT
TABLES - but not in the junction table! You *want* to allow either
field individually to allow duplicates (M or N duplicates
respectively). The uniqueness of the combination is enforced by the
Primary Key index on the junction table.

My question is twofold:
1) Should those fields be indexed explicitly again in the junction table in
access?

No. Creating a Relationship in the relationships window will create
the desired nonunique index.

2) Speaking outside the framework of access and in the more general context
of database theory, what is the preffered methodology on such composite
keys?

Exactly what Access sets up: a unique Index (created by defining a
primary key) on the combination of the two fields; nonunique indexes
on each field individually, which Access creates when you define the
relationship.

a. Create a combined index on the composite key?

You already have one - it's called PrimaryKey and need not be created
separately.

b. Create two separate indexes for each attribute of the composite key?

You already have them - if you've enforced relational integrity.

 

[Kostas]

Thanks to you too John as well as to Brendan.
Very clear answers, I got my query answered 100%.

Kostas