Hi Walter

I agree it all depends. There are no hard and fast rules when working in an environment without any standards whatsoever. I have built systems with both approaches, because I want my systems to be as good as they can be. I've tried to switch between the two designs in the last few years, largely based on your input. I've read many if not all of your posts against integer keys and tried to prove to myself that your position was correct. Sometimes the lone voice is right and too often the lone voice is ignored. But it has been my experience that I've wasted lots of time in trying to prove one way is better than the other. IMO, more often than not, the benefits of integer keys outweigh the drawbacks you point out.

Out of respect for your obvious skills, I just wasted another hour trying to find an example where your statements would be correct. I had to make a meaningful character key on a table that had none. It had to be unique, but with some matches on the leading characters. After building that key, I ran a query to determine if there were many duplicates of the first 5 characters. Out of 37654 records, the query returned 14316 records. So almost 40% of the records have matches on the first 5 bytes.

I built three indexes on a table with 37654 records. One on upper(company_name) the 2nd on integer company_pk and the 3rd on a unique 10 character id field based on the first 5 chars of the company name + alltrim(str(recno())) to three different .CDX files. The company name .CDX file size was 660480 bytes. The company id file size was 318976 bytes and the company pk cdx file size was 165888.

I then took an integer key that represents the company type. The company types are stored in a lookup table. I added a 4 byte field to the company table. I set a relation into the lookup table. I took the first 4 bytes of the lookup description (which is the company type description) and put them into the new field in the company table.

I built an index on the 4 byte character field to a new .CDX file. I also built an index on the already existing integer company type to another new .CDX file. The size of these two files was identical despite heavy duplication of the keys.

It is a real company table, so I'd have to remove information before I could send it to you to confirm. But I would welcome your proof.