Primary key: code or name?

One of the most common kinds of d开发者_高级运维atabase table has an alphanumeric code and human friendly name (e.g. countries, currencies, accounts, products, VAT codes etc.)

The traditional/obvious thing to do is make the code the primary key. And for some tables e.g. customers where the number may be large so that names may not be unique identifiers, this is clearly the correct thing to do.

But what about things like countries and currencies where the number is guaranteed to be small and the names are guaranteed to be unique? In that case, references will almost always be both input and displayed with the human friendly name.

In that scenario, is there any reason not to make the name the primary key?

Never, ever, use a business value as a key.

Here's why: No matter how sure you are that the customer number is immutable, sometime in the future a customer number will have to changed (Chinese customer gets 4444, which is very unlucky, whatever). If you've used 4444 as a key, you'll have to change not only the customer's key, but also the related records in his orders, his addresses, etc.

(Some will argue that this can be resolved with cascading updates, but it's risky in the presence of triggers.)

Best practice: Create a surrogate key and call it ID (some prefer CustomerID). A surrogate is a key which is hidden from the users (thus its name) and whose only purpose is to provide a unique key. This allows you to make unambiguous joins and deletes without worrying about what users might change.

Every table should have exactly one surrogate primary key and it is either an auto-incremented integer or a GUID (varies according to the database provider).

There is only one allowable exception to this rule: when creating a N-N relation (e.g. one customer can have many addresses and addresses can be shared by customers). In this case it is acceptable to use the [CustomerID, AddressID] pair as the primary key.

Oh, and finally, joins on integers/guids, which are fixed-length are much faster than joins on varying length strings.

A few thoughts... If you're interfacing with other applications then the ISO standards for countries and currencies would be better received.

Although such data is more or less static ... country names do change, e.g. Ceylon to Sri Lanka, Rhodesia to Zimbabwe, so you would need to update in many places rather than just the description on your lookup table.

Input/display are not always undertaken using the friendly name, e.g. data entry of currency where the users are comfortable with this.

If picking a country from a list then its fairly trivial to have this translated to a code under the bonnet.

if you're going to be using the value of these fields in other tables, then you would be violating normalization principles of database design, specifically, you would have same data in multiple places. This is bad for several reasons:

1. numbers are faster to search than strings
2. strings take up more memory than numbers