I'm missing something here, Craig

If I have ONE table with 50,000 "live" parts in it with 16 char part numbers, and decide to use a 6 char shortcut, there isn't much chance of a collision, so a key will work well.

BUT if I have TWO tables, one live with 50,000 records, and another an archive going back to day one , and holding 5,000,000 records and growing, it seems to me the key has to be on the archive file rather than the live file.

BUT, I hate the idea of making users access the archive file just to get a new key, since in my experience, these big files seem to be the ones that are most likely to be locked simply because it takes so long to process them for reports, as well as the extra burden on the network!

So I tend to use a small control file to keep track of the cross reference rather than open the archive. ( linked list concept, also allows for multiple archives, less indexing needed )



I'm basing this on my work experience at GM and Xerox. Part numbers track the supplier and destination as well as the part. Production only cares about the "stub", but procurement need to know the rest of the info, and need to check history,

Use of the "short" number resulted in the wrong parts arriving! A unique stub IS quite easy to create, but it required cross refencing a number of different files .

Anyway, I'm here to learn, so I wondered how you deal with the real world problems of different groups using the short names


Mike