ISO Country codes, language codes and currency codes are so common (and don't change very often) that they should really be part of the databases's standard distribution as types (eg, enums), so that everyone is singing from the same hymn book and that not every developer needs to implement the same tables in their DB.
Re:SQL Keys in Depth
by 9034725985 on Tuesday January 02, 2018 @03:23PM
(#16055231)
Are you thinking something like pg_timezone_name?
https://www.postgresql.org/docs/current/static/view-pg-timez...
I feel like an ISO country code is more of a surrogate key anyway; it's a mnemonic made-up value specifically designed to be used as key.
Re:SQL Keys in Depth
by dragonwriter on Tuesday January 02, 2018 @04:07PM
(#16055638)
An ISO country code isn't a surrogate key; it's not db-specific, and it has domain meaning (assuming you are using it for it's intended purpose). It's ususlly, when used, a representation chosen for natural domain information, so if it's a key, it's a natural key.
From the article:
The naturalness or artificiality of unique properties in a database is relative to the outside world. A key which was artificial at birth in some standards body or government agency becomes natural to us because it’s generally agreed upon in the world at large, and/or imprinted on objects.
I was a fan of natural keys but it is just too much trouble. For example you have to url encode everything when a key is used in the url.
But sometimes I still use natural keys for tables with for example ISO standards like country codes.