Indexing (1 Viewer)

[geister]

Hi there,

I am new to access and learning it because I want to understand relational databases without getting into SQL just yet.

I have a few areas that confuse me, but one that makes little sense to me is indexing. The answer is maybe obvious, but what difference does it make to allow duplicates or not?

I know it sounds dumb but it really hurts my head why this matters.

 

[pbaldy]

If I have a customer table and customer number is my key, I don't want duplicate customer numbers. If there are 2 customer number 123's, how do I know which one you want when you type in 123? How can I use that field to relate to their orders if customer 123 in the orders table could refer to more than one customer?

 

[geister]

If I have a customer table and customer number is my key, I don't want duplicate customer numbers. If there are 2 customer number 123's, how do I know which one you want when you type in 123? How can I use that field to relate to their orders if customer 123 in the orders table could refer to more than one customer?

So it is kind of another way of enforcing integrity?

 

[pbaldy]

Certainly; the database engine protects you from having duplicate key values.

 

[geister]

Thanks both, I should have trusted my own understanding here rather than overthinking it.

 

[geister]

One follow up, when ye are deciding what to index - at the most basic level - what are you looking for?

It seems to me that most small databases would not need to pay attention to this (leaving aside primary key auto-indexing).

Are there any general rules for thinking about when and when not to index?

 

[CJ_London]

index any field

1. where you regularly join on in queries (so both sides of a one to many relationship)
2. where you regularly filter on
3. but not where that field has a limited range of values, such as yes/no

this link might help explain a bit

https://www.access-programmers.co.uk/forums/showthread.php?t=291268

 

[pbaldy]

FYI, approved the moderated posts that CJ responded to.

 

[CJ_London]

missed that

 

[Galaxiom]

index any field where you regularly join on in queries (so both sides of a one to many relationship)

However, be aware that defining a relationship automatically places indexes on the related fields. Be sure you don't duplicate these automatically created indexes.

 

[CJ_London]

defining a relationship automatically places indexes on the related fields

I always thought that was to do with settings in Options>Object Designers?

 

[Galaxiom]

I always thought that was to do with settings in Options>Object Designers?

You mean the setting that interprets certain names such as "ID" as fields that need to be indexed?

That is separate from the indexes generated by creating a relationship. Create a relationship then look at the indexes on the tables. I understand these indexes were hidden in the user interface of earlier versions of Access but could be seen in VBA.

 

[CJ_London]

You mean the setting that interprets certain names such as "ID" as fields that need to be indexed?

yes but although it doesn't say so, it applied an index if it starts or ends with ID

IDField - indexes
FieldID - indexes
FIDield - not indexed

 

[geister]

I'm ignoring the last few lest I end up confused!

How about when to allow duplicates or not?

Also sorry for the dumb questions. I'm from the school of just ask, no matter how dumb.