One more index question.

[Kostas]

I was told by some helpful folks in here that I shouldnt explicitly define
indexes (dupl. ok) for each field in a junction table (m-m relationship) as
access automatically creates hidden indexes for them when referential
integrity is enforced.
Is it safe to suppose that the same happens with simple foreign keys in a
1-N relationship? For instance, assume one table Dept and one table
Employees. One dept has many employees and one employee belongs to one only
department. DepartmentID is a foreign key in employees. Do I need to
explicitly specify an index (duplicates ok) for this field in the employees
table? Or should I just link the fields in relationship window and enforce
referential integrity?

And one more thing: If Access creates hidden indexes in these situations
why doesnt it make them obvious by changing the index field value to "Yes"?

Thanks,

Kostas

 

[gandalf]

I would create the indexes myself instead of relying to
Access to create "hidden" indexes. (Access won't overwrite
existing indexes I hope)
This will also more helpfull if you decide to switch to
another database-engine.

If you doubt that an index is necessary, you could try the
performance analyser in ACCESS.

 

[Kostas]

As far as I understand this though, even if one does not define an index
that theoretically should have, they can still define it later and the db
will create it.
It doesnt seem to be as serious a mistake as one that results from bad
conceptual design and might have you changing the whole structure of the
database.
Thank you for your reply, appreciated.

Kostas

 

[Brendan Reynolds]

Access will not overwrite an existing index, but it will create an identical
index with a different name. Both indexes will have to be updated whenever
the data changes, so there is a performance hit, and no compensating
benefit - what's the point of having two identical indexes?