[AccessD] Primary Key Best Practices

On 25 Jul 2007 at 13:12, Arthur Fuller wrote:

> rows is fewer than 500 or so. It's silly. Disk space is cheap. Take the case
> of a table called State/Provinces, consisting of about 62 rows representing
> the states in USA and the provinces and territories in Canada. All of these
> items can be represented uniquely by a two-letter combination. Why on earth
> would you then introduce an ANPK into this table? It makes NO sense, IMO. It
> rather exemplifies the ridiculous application of a maxim learned long ago
> and adhered to since, without further examination.
> 

Now imagine that your system expands and you need to another country 
in addition to US and Canada - what happens when another country has 
the same 2 letter abbreviation for one of their states/provinces?

> Let's go further. Suppose your firm has 100 products all of which are
> uniquely identified by three alpha characters. Why would you bother creating
> an ANPK on said table? IMO it's asinine. You just force me to create joins
> down the road (as in reporting), while providing me with no real gain in the
> here and now. You wanna benchmark your index on an ANPK versus mine on a
> three-letter alpha column? Ok? Let's go. I'll give you half a second
> maximum, and then let's look at the rewards I get that you don't.
> 

Been there, done that - after a few years, they merged with another 
firm and the codes had to be changed.  Hell of a job to rebuild all 
the relationships.