0

I have or am going to have a database with countries and their emissions. Countries in the API have a 3-letter ISO Code. Should I use this for identifying the countries in database, or just a plain numeric id?

    CREATE TABLE country
        (
            countryid integer PRIMARY KEY NOT NULL, 
        ),

    CREATE TABLE country_emissions
        (
            id integer PRIMARY KEY NOT NULL,
            countryid id NOT NULL references country(countryid),
            year smallint NOT NULL, 
            emissions integer 
        ),

So, could countryid in this case be replaced with countries' respectable ISO code?

Reason why I'm not sure is cause numbers seem more logical as you can't really typo them, where as you can have the ISO code written with lower case letters, etc. But then again, you wouldn't have to fetch the numeric ID of a country before adding emissions, you could just straight add them with the country ISO code.

  • 1
    ISO 3166 specifies both numeric and alphanumeric codes for countries. – Pieter B Jan 14 at 10:08
  • @gnat its the same question, but the answer totally ignores the int vs something else thrust of it – Ewan Jan 14 at 11:04
  • 5
    Never use, as a primary key, values controlled by another party. Sooner or later you will have a good reason to add your own values, possibly also to delete existing ones. Sooner or later the other party will make changes which you don't want to track. – High Performance Mark Jan 14 at 13:11
4

When you ask specifically about the ISO 3166 country codes and using them for a primary key in the database containing emissions data you should consider how static these codes are.

As an example (I haven't been able to verify that Sudan has not changed code as I have failed to look up any older specifications) Sudan (SDN) was in 2011 split up in two countries Sudan (SDN) and South Sudan (SSD).

If you in this case use the country code you may see that Sudan in 2011 suddenly drops in emissions, and your database do not give any explanation. There isn't anything in your model that can show you what has happened.

A different example is The Czech Republic (CZE) and Slovakia (SVK). In this case one country code (CSK) was split up into two new codes.

What you really need is to model how countries can merge and split. And in that case I would model Sudan prior to 2011 as a different entry in the database as Sudan after 2011, though of course there would be a clear reference between the two, both having the same country code.

You can then use the same references to model the break up of Czechoslovakia.

As an example of how complex a break up can be you can look at how Yugoslavia was broken up and when each part got their country code and how "Macedonia" has changed names and still is changing.

  • 1
    Wouldn't the subdivisions and relationships between countries would remain an issue regardless of whether the country id is a ISO code or a int? Either way you'd need to introduce references to other countries and form some kind of tree/graph/timeline. – Dev243 Jan 14 at 19:03
  • But you can't use the same ISO code for more than one entry in the database if it is the primary key. – Bent Jan 14 at 22:01
  • The country codes, al thought very rare, can change, so I think I will stand by your point and not use the ISO code for identifying countries, as was also in the other linked thread. – user3553653 Jan 14 at 23:47
0

According to pure database concepts, you should not use ISO country code as a primary key. You should use integer id as a primary key in your database tables. You can not implement auto increment in ISO country code as it may differ from country to country.

New contributor
Ishan Shah is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
-1

As has been pointed in the comments, there are specifications for both numeric and alphanumeric codes. You could use both.

But in the end, it depends on what you are doing, the scope of your project and who will use the code.

If you are in doubt, I would use the alphanumeric code since it tends to be more clear. You could check the case of the argument setting everything to upper case before making the call to the database. You should sanitize the arguments anyway right? My two cents.

protected by Robert Harvey Apr 18 at 4:16

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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