As one of this forum's resident pragmatists, I feel I should offer a viewpoint just to include diversity.
The first and foremost rule is to know your data. But also, it helps to think about how it is stored, and that information IS available in the help files if you weren't sure.
Meaningful keys often present themselves in a way that makes perfect sense to use them as such. Meaningless keys operating "behind the scenes" strictly as a matter of formality make no sense.
Take the obvious case: State abbreviations. They are two characters long, whereas an autonumber is 4 bytes. Indexing is equally easy for two-letter fields as it is for autonumbers and is equally likely to be unique and invariant once defined. OK, the USA could add another state, but otherwise there is no reason to avoid a meaningful SHORT key.
One contrary example would be something based on, say, social security number, which in most USA locations is forbidden as a formal identifier for lookup purposes. (Not to mention impractical, since it is possible for recent, legal USA immigrants to not yet have one.)
So when DO you use meaningful keys? Agreed that you never really MUST use meaningful keys UNLESS your company has a guideline manual that spells it out as to when/where you use them. Having said that, I have no qualms about using a meaningful key when it makes sense to do it, particularly for short-code or small-table lookups.
Just review what a key is supposed to do: It is supposed to lead you absolutely and unequivocally to one specific row in the appropriate table. When screening for candidate keys, if you have one don't just ignore it
pro forma. Skip it for practical reasons.
Could it change in the future? If so, don't use it. But if it could expand and you have the room, that's not a rejection excuse. (I.e. so the USA really DID add another state. We add another abbreviation to the states table and we are done.)
Is the candidate key too long to be useful for efficiency? One LONG integer is your limit there UNLESS you know for an absolute fact that the table will be incredibly sparse, maybe only a handful of entries.
Here's a poser: Even for long candidate keys, is there a significant chance that other indexes on the same table will require you to exceed the allowed number of indexes per table? (Yeah, I know, should be rare... but it happens.) In that case, consider how many JOINs we are talking about for the long, meaningful key. If it is a very small number, and particularly if the child tables are also very sparse (i.e. a smattering of optional data), you MIGHT (I emphasize
MIGHT) consider it.
I never thought I would ever see a setup where more than ten indexes would be on a table, but then I went to work in a Dept. of Defense environment. HOLY GUACAMOLE! I no longer make rash assumptions about how many indexes one needs on a table until I actually analyze the requirements statements and consider the performance expected out of the system.
Anyway, the point is that for really complex situations, meaningless keys are really preferred to make it all tie together much easier - but there will always be times when a meaningful key approach still works right.
NOTE to readers who don't remember or never saw the thread: In the Design section, if you do a search for "Meaningful keys" and "Meaningless keys" and if you change the time limit to reach pretty far back, you will see several long threads on the subject that give pot-loads of opinions and viewpoints. One advantage of being the old-timer: You remember a few of the good threads.
