[GIS] Should I use ISO 3166-1 Numeric codes as Primary Keys for Countries in a database table

database-design

My main concern is, will one country ever adopt a numeric code that has been used for another in the past, such as can happen with alphanumeric 3166-1 codes? If not, then it seems like a perfect identifier for a primary key in a database…

Best Answer

Coming from a pure database perspective. No you should not. For the following reasons:

  1. According to this: http://en.wikipedia.org/wiki/ISO_3166-1_numeric#Deleted_codes some codes have been changed. While this will create a mess in all systems this mess will be compounded by the fact that primary keys tend to be harder to change than normal columns.
  2. Security reasons: see this: https://stackoverflow.com/questions/7451348/should-primary-keys-of-mysql-tables-be-exposed
  3. Have something such as a primary key under the control of any other organization can lead to trouble.
  4. Strings as primary keys are less "efficient" than numeric keys. I am basing that solely on the fact that a string or character representation needs to be unboxed when used in code and am not talking database implementation. It is also a string since some country codes are prefixed with a zero