Countries Table - Design Thoughts (1 Viewer)

[Minty]

Hi All,

I'm undecided about this so thought I'd ask the great and the good, bad and the ugly...

I need to have a reference table for countries (and various other fields) and am wondering do I add a primary key ID and use that or store the ISO country number?
Previously I have only ever need a country name so simply had that in a look-up table without a PK.
In this case, I need to retrieve different info from the table on a very frequent basis for currency info etc.

Sample below.

Country_name	alpha-2	alpha-3	country-code	iso_3166-2	region	sub-region	intermediate-region	region-code	sub-region-code	intermediate-region-code	CurrencyName	CurrencyShortCode	ISO_CurrencyCode
Afghanistan	AF	AFG	4	ISO 3166-2:AF	Asia	Southern Asia		142	34		Afghani	AFN	971
Åland Islands	AX	ALA	248	ISO 3166-2:AX	Europe	Northern Europe		150	154		Euro	EUR	978
Albania	AL	ALB	8	ISO 3166-2:AL	Europe	Southern Europe		150	39		Lek	ALL	008
Algeria	DZ	DZA	12	ISO 3166-2Z	Africa	Northern Africa		2	15		Algerian Dinar	DZD	012
American Samoa	AS	ASM	16	ISO 3166-2:AS	Oceania	Polynesia		9	61		US Dollar	USD	840

 

[arnelgp]

better add the id, the countries are static, unless a great eruption occurs and create one.

 

[MajP]

Is your question about using Autonumber or use the natural key ISO 3166-2.

I almost always use the autonumber. Because often when I do not, I get proved wrong. Some reasons

1. You always think when entering data you will have the natural key, except when you do not. So someone I as working with said they always had unique, non changing, employee ids. So I told them that is a good key. Then they got back to me and wanted to know what to do for a couple new employees that the corporate office had not yet assigned IDs and wanted to get their information added. Then when they finally got the IDs some where bad/duplicates. So they ended up not being able to enter the employee info for a several days.
2. I also get nervous when I see a natural keys with lots of characters Never know what is going to cause problems for joins and searches.
ISO 3166-2:AX's #2

 

[Minty]

@MajP The International country code (column 4) is allegedly fixed, unchanging forever, ISO agreed with standard.

So based on that statement and your own experience and my own - I'm going to add a PK ID

 

[MajP]

Like I said, I always get fooled. Every time I think it is Unique, Available, Non Changing, and Simple (not super long and lots of weird characters), I get proved wrong.
So yes it looks like a perfect candidate for a natural key and you probably be safe. But there is always that 1% chance. and you really are not losing anything with an autonumber.

 

[Steve R.]

A redundant, thumbs-up on using your own primary key, not the the international country code.

 

[jdraw]

I would opt for an autonumber also. I have seen changes to Canadian provinces and territories, as well as changes to ISO codes. Not too frequent, but....

 

[Isaac]

Agree, natural keys are accidents waiting to happen, you gain absolutely nothing from taking the risk, and add the burden of trying to defend or explain people's expectations as to its meaning

 

[Pat Hartman]

Let me jump on this train in case there aren't enough comments. Use your own autonumber PK within your database. Make a unique index on the ISO country code. NEVER trust anyone else's "unique" PK. They'll change it if they find a reason to and it doesn't matter that the change messes up your structure. As long as their "PK" is stored as a text string in your database, they can't hurt you. I keep waiting for the SSA to announce that SSN will at some point be alpha because they've run out of of unique values using only numbers. I've never created a table that stores SSN as a number but I have seen others do it.

You might want to normalize the table also. Currency belongs in its own table as does subregion.

 

[Minty]

@Pat Hartman - Thanks for the reply, I have added the index and my own PK.
The currencies are mainly for reference at this point and will be separated off in the final design, as will the regions. I have subsequently discovered the regions don't match the customers' descriptions or areas so much easier to adjust the child region tables.

The currencies are also complicated by the customers' final use - they use a local currency for input but the payout is either £ $ or Euro depending on the region that is doing the administration. I love multi-currency systems

 

[gemma-the-husky]

I suspect an ISO country code will be permanent enough. If a country changes, and they issue a new code, you would need a new record anyway.

I would have thought this was directly comparable to US states, say, and I doubt many developers add a numeric autonumber to a US states table - but maybe they do?

You can never go wrong by having your own ID code for a country though. It may add a layer of confusion to anyone inspecting a table and not seeing expected abbreviations, but it won't affect the way the dbs works.