SQL static data / lookup lists IDENTIFIER

Question

In regard to static data table design. Having static data in tables like shown:

• Currencies (Code, Name). Row example: USD, United States Dollar
• Countries (Code, Name). Row example: DE, Germany
• XXXObjectType (Code, Name, ... additional attributes)
• ...

does it make sense to have another (INTEGER) column as a Primary Key so that all Foreign Key references would use it?

Possible solutions:

1. Use additional INTEGER as PK and FK
2. Use Code (usually CHAR(N), where N is small) as PK and FK
3. Use Code only if less then certain size... What size?
4. Other _______

What would be your suggestion? Why?

I usually used INT IDENTITY columns, but very often having the short code is good enough to show to the user on the UI, in which case the query would have one JOIN less.

Answer 1

An INT IDENTITY is absolutely not needed here. use the 2 or 3 digit mnemonics instead. If you have an entity that has no small, unique property, you should then consider using a synthetic key. But currency codes and country codes aren't the time to do it.

I once worked on a system where someone actually had a table of years, and each year had a YearID. And, true to form, 2001 was year 3, and 2000 was year 4. It made everything else in the system so much harder to understand and query for, and it was for nothing.

Answer 2

If you use a ID INT or a CHAR, referential integrity is preserved in both cases.
An INT is 4 bytes long, so it's equal in size as a CHAR(4); if you use CHAR(x) where x<4, your CHAR key will be shorter than a INT one; if you use CHAR(x) where x>4, your CHAR key will be greater than a INT one; for short keys doesn't usually make sense to use VARCHAR, as the latter has a 2-byte overhead. Anyway, when talking about tables with - say - 500 records, the total overhead of a CHAR(5) over a INT key would be just 500 bytes, a value hilarious for database where some tables could have millions of records.
Considering that countries and currencies (e.g.) are limited in numbers (a few hundred, at most) you have no real gain in using an ID INT instead of a CHAR(4); moreover, a CHAR(4) key can be easier to remember for the end user, and can ease your life when you have to debug/test your Sql and/or data.
Therefore, though I usually use an ID INT key for most of my tables, in several circumstances I choose to have a PK/FK made of CHARs: countries, languages, currencies are amongst those cases.