It's part science, and part art; as with anything where aesthetics get
involved, tastes differ.

If some of your lookup tables are just a word for each record, and you
can count on that word being pretty stable, and can ensure that it is
unique, then you could very well just use a single-field lookup table.
E.g. your PaymentTypes table could have one six-letter Text field as
its primary key, with values Cash, Check, VISA, MC, AMEX, DISC. It's
short, it's stable, and it's unique; and yes, it will save you one
more join in your reports. Another example is US States (and Canadian
provinces) - they have a unique two-letter Postal Service code which
is unique, short, and *almost* completely stable (Canada changed the
codes for Nunavut and Labrador a few years back).

Cities, on the other hand, are iffier. I believe that there are cities
named Springfield in almost - or every - state in the US, so city
names per se are NOT unique. (There are even two cities named Los
Alamos in New Mexico).

Where "surrogate keys" like Autonumbers shine is for entities like
people's names: names are not unique, they are not stable, and they
are not particularly short, so they fail on all three criteria for a
good key.

But you're right - it is certainly NOT essential to have an autonumber
PK for every table!

                 John W. Vinson[MVP]