Am I indexing unnecessarily

[NeilT123]

I am self taught in Access and have over the last couple of years built a database that I use daily.

I now want to carry out some maintenance and having looked at some of the original tables I created I wonder whether I am creating unnecessary indexes.

For example I have a table tblFertProduct the first entry is ProductIndex which is an autonumber, PK and is indexed(no duplicates).
The 2nd entry is productName which is a text data type and is also indexed(no duplicates).

The productName could be up to 50 characters long and includes spaces, hyphens and brackets but is unique.

Do I need both would I see any drop in performance by making ProductName the PK and deleting ProductIndex?

There are a number of queries that would use the PK as a join so am I asking for trouble by moving from an Autonumber to a text field?

My database is Access 2007, is 29,000KB and is used by a max of 3 users at any one time.

Thanks for any help and advice

Neil

 

[Golom]

Technically at least, you probably don't need the autonumber field. Two indexes on a table is however, not a major concern and when a database is as small* as yours they are unlikely to be causing noticible performance degredation.

If there is significant modification required (e.g. code changes, modified queries) resulting from a structural change I would probably just go with what you have.


* ~29MB is a fairly light load for Access although the number of records in the table is a more relevant measure as is the rate at which additions and/or deletions are made.

 

[NeilT123]

Hi Golom, thanks for the response.

I will have to investigate how much modification is required before starting on any changes.

I am learning all the time about what Access can and can't do and it would certainly be easier for me to be able to use product names/descriptions rather than index numbers.

My main concern was using a field containing hyphens spaces and brackets as a join, which by the sounds of it is not a problem.

Thanks

Neil

 

[alvechurchdata]

I'd advise against getting rid of productIndex and using the productName as the primary key. You say that the name is unique but one day somebody will try to add a duplicate name or - worse still - you'll find that somebody has an absolute business need to change one (or all) of these names. Removing the productIndex would make the database a little bit smaller but that integer field isn't taking up much space and it might just save you an awful lot of work in the future.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[boblarson]

I am a proponent of a surrogate key (autonumber) because of

1. What Geoff said - eventually business rules seem to change and if you base your key off of something that could change it can mess things up.

2. It is okay to have a multifield index to ensure that duplicates for that combination are not entered. However, you can also, depending on how your database is used, leave that to the Before Update event of a form and therefore you can leave off the multifield index.

3. If you choose to remove the autonumber and go with a composite key like Golom suggests, be aware that you have to store all of the fields that make up that key in other tables if used as a foreign key. This can not only be a waste of space and processing but also can complicate your queries as you will need to link on all of the composite members where you would only need to link on the autonumber/long integer PK/FK.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[lameid]

The number index is also going to be faster for joins as opposed to the text... Just think about the length of the keys... A long integer / Autonumber is I think 32 bits or 4 bytes... A 50 character field is (upto) 50 bytes. A join is effectively going to sort two lists and try to match intelligently, the smaller this key, the better off you are.

That is on top of the storage requirements Bob Larson mentioned.

I would not change anything unless you run across a reason to remove the uniqueness from your text index.

Now to play devil's advocate, if you find yourself only joining to tblFertProduct to obtain the values you would use as a key, you will see a performance advantage in reports by reducing the tables and therefore joins involved.

This is denormalization for the merit of reporting. The rule of thumb here is do not do it unless you have a very good reason and you have thought about the fall-out.

But like Golom said, on such a small database you probably are not going to see a noticeable difference either way.