View Single Post
  #1  
Old May 11th, 2010, 04:29 PM posted to microsoft.public.access.tablesdbdesign
Petr Danes[_5_]
external usenet poster
 
Posts: 31
Default To index or not to index

I have a table of stuff stored in a repository and an attached table of
inventory dates, linked one-to-many by an Autonumber ID field. I regularly
need to find the oldest or newest inventory dates (or all, in order by date)
for each item record in the inventory table, which is normally an automatic
case for indexing. But this stuff is not inventoried very often, so far,
only two out of over 80,000 records have three records in the inventory
table, all others have zero, one or two inventory records. This is NOT going
to change. It will likely be decades before there are as many as ten
inventory records for any item record, and then it will not be for very
many. I doubt if this database will live to see the day, although I'm trying
to make it as useful and robust as I can.

Given such a small number of detail (inventory) records per item record,
does it make any sense to create an index on the date field? I only need to
look up records in conjunction with the master item record, never by date
alone.

Pete

--
This e-mail address is fake, to keep spammers and their address harvesters
out of my hair. If you want to get in touch personally, I am 'pdanes' and I
use yahoo mail. But please use the newsgroup when possible, so that all may
benefit from the exchange of ideas.