>Suppose you have a lookup table with one character field. Selections from this table are entered in a related field in some other table. If the strings in the lookup table were really long, most of us would make a keyfield for the lookup table and store that key in the related table, instead of the long string. But many would not bother if the strings were only a few characters. For the sake of speed, where do you draw the line? Six characters? Twenty? Also, does the number of records in the lookup table make a difference?

I draw a line at zero - I allways use the keyfield. No matter that the text behind the keyfield is short - it's short now, but what if the items in the list need extending in the future? It's much to easy to add some records to it or expand the text field. Bad side of this is that you open two files more, when sometimes you don't need to.

The only case when I don't use lookup tables at all is when I use arrays (defined somewhere early in startup) and numeric values as keys to them, and I use them only when I'm sure the list won't get too long, and that it's probably prone to very little change through the years. Of course, nothing ever gets deleted from the array - its contents can only grow.

For one, I use it for things I define programmatically, or the cathegories of things defined by law (though this changes too). I'm still not sure I did the right thing for defining such an array for social security category - it has both long text and increasing number of lines (12 now, since refugees and those sponsored by UNHCR were added to the list).