Referencing countries via Foreign Key Constraint

Question

To make things simple to explain: I've got two tables: Table1 and Countries.

The Countries table contains a nice flag and some information, such as DisplayName, the ISO3166 ALPHA3 code and the phone code (+49, +xx).

Table1 contains customers with a column Country.

Is it (in terms of usability, normalization and common human sense) okay to reference from Table1.CountryId to Countries.Id via a foreign key instead of having a plain Table1.Country nvarchar(255) field and insert a string into that?

Answer 1

Not only is that ok, you absolutely should do it that way.

Only the Country table should have information about countries (such as the name). All other tables that need to store a country should simply use CountryId, and this should be a foreign key to the Country table.

The reasons for doing this:

• It avoids duplication. It is a waste of space to store the country name and other similar info in multiple places. Storing the country name in both the Country table and elsewhere can use many times more space. Suppose you have a million customers from a hundred different countries. If you store the country name in the customer table, you have just increased the storage space used for country name by a factor of 10,000!
• It removes ambiguity. If you store the country name in two places, you run the risk of having multiple versions of a country name. You might have "USA", "U.S.A", "United States", etc. This will be a mess to deal with. Just using an ID field avoids this problem.
• It is efficient. Perhaps someone's reason to store the country by name in Table1 would be "well, then I don't have to join all the time to get the country name". However, this is a false economy. A join based on a numeric ID that is set to up as a foreign key will be trivial, performance-wise. On the other hand, if you stored the name in both places and then needed to join based on the name, it would be far slower.

Answer 2

Identifying countries by a foreign key is an excellent idea. Using anything out of ISO 3166 is not:

• The standard allows the same alphabetic codes to be re-used when a country undergoes a change. For example, when East and West Germany were united, the entries for both countries were retired. The new country retained West Germany's alphabetic codes (DE and DEU) and was given a new number. The same happened when South Sudan split off from Sudan a couple of years ago: Sudan's number was retired, new numbers were issued for both countries and Sudan kept its old alphabetic codes.

• Some alphabetic codes may be re-used for other countries in the future. The codes for Burma (which renamed itself Myanmar and retained its number) are transitionally reserved until 2040, when they go back into the pool and could potentially be issued to some other country.

• The numeric codes have historically been unique, but not having a copy of the standard, I can't say for sure if they're guaranteed to stay that way. With numbers in infinite supply, I imagine they won't be re-issued, but I wouldn't bet the integrity of my database on it. The standard follows the numbers assigned by the United Nations Statistics Division, and the UNSD has already re-used the code it used for Spanish North Africa (a territory) for South Sudan.

Bottom line: maintain your own table with its own keys.