Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 6 down vote accepted

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.
share|improve this answer
    
Regarding the join efficiency: The least efficient way is to try to join two tables on a string that might contain typos/spelling variations. –  Bart van Ingen Schenau Apr 26 '13 at 9:57

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.

share|improve this answer
    
+1 for this very useful information. I have dealt with a similar issue (British postcodes that can be reassigned) and it is a potential nightmare. I think this should definitively decide the issue. –  dan1111 Apr 29 '13 at 7:41

I agree with @dan1111's answer, but would add that in your particular case a column called Countries.Id is superfluous.

In the Countries table, you already have a primary key in the country's ISO code (http://en.wikipedia.org/wiki/ISO_3166-1_alpha-3). You say you have this information anyway, so what's the point in having an artificial id in another column?

If you're worried about typos, then there's nothing to stop you pre-populating Countries with a row for every country (where you're guaranteeing that the code is correct).

Note that the same cannot be said for the dial code - for example the USA and Canada share the same international code.

But don't be afraid to use strings as key values when it is appropriate.

share|improve this answer
    
Using the ISO code would work as long as you are sure that your concept of "country" will always map to that standard. But that might not be the case. In the UK, Scotland, Wales, and Northern Ireland are considered countries in some contexts, but they don't map to that standard. Or a U.S. company might count things like "U.S., except Alaska and Hawaii" or "U.S. military overseas" as a country for shipping purposes. –  dan1111 Apr 26 '13 at 11:46
    
yes I agree it would depend on @setoy's concept of a "country" conforming to international standards. I would assume that where shipping is concerned, one might use something other than a list of countries, although I am not aware of any standard. –  PeteH Apr 26 '13 at 11:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.