DATABASE DESIGN - Primary key for COUNTRY, CURRENCY int or varchar_问答_开发者

For my country table, I used the country code as the primary key "AU, US, UK, FR" etc
For my currency table, I used the currency code as the primary key "AUD, GBP, USD" etc

I think what I did is ok, but another developer wants me to change all the primary keys to开发者_JAVA技巧 an int, because the country code, currency code might change sometime in the future he said. We just don't know that, well in this case he is right, his path is the safest path to take.

Should I change the primary keys to an int to be safe rather than be sorry? Can't I just keep it?

I would use the ISO codes with char columns.

If a country ever splits then you'd get new ISO codes (say SC, WL, EN) but UK will still be valid for historic data.

It's the same for currency. A transaction in 2000 would be in the currency at that time: French Francs, Deutschmarks, Belgium Banana but not Euro.

I would say the "birth of a nation" or the disappearance of a currency is - over all - a rather rare occurence - not likely to happen several times a year, every year.

So in this regard, I would think using the ISO defined country and currency codes for your primary key should be OK.

Yes, if something happens to the Euro zone, or if another country is split into two, you might have to do some manual housekeeping - but you'd have to do this with an INT as well. In a case like this, I would argue that an artificial surrogate key (like such an INT) really only adds overhead and doesn't really help keep things easier/more explicit.

Since those codes are really short, and typically all the same length, I would however recommend using a CHAR(3) or CHAR(5) - no point in using VARCHAR for such a short string, and also, VARCHAR as variable length fields do behave quite differently (and not "better" in terms of performance) that fixed-length fields like INT or CHAR

From a logical point of view, adding a surrogate means extra columns, additional key constraints and more complex logic to query and manipulate the data. That's one thing to consider.

From a physical standpoint, in SQL Server an INTEGER key will take up more than twice as much space as a CHAR(2) or CHAR(3). That means your referencing tables and indexes get larger. It also makes any updates to those foreign key values much more expensive. I don't know your data but it seems quite possible that the referencing data in those foreign key columns could be updated much more frequently than the country code and currency code values in the parent table. In contrast, the ISO codes for currency and country almost never change so that is probably very little to worry about. By changing to INTEGER keys you could very well increase the cost of updating those foreign key values.

If you are considering such a change as a performance optimisation then I suggest you evaluate very carefully whether INTEGER keys will make updates of those values more costly or less costly. I suggest you ignore people who say "always do X". Dogma is no help in database design. Evaluate what the real impact will be in practice and make your decision accordingly.

I think that's your system will become obsolete ten time before the ISO standard about country and currency code will do.

So I really don't see any benefit for using 01010101 01010011 or 21843 instead of "US".

So long as any foreign keys that reference these primary keys are declared with ON UPDATE CASCADE, who cares if these codes change?

There's an additional benefit in querying any of the referencing tables - if all you need is the country/currency code, then there's no need to join to these tables - you've already got the code included in these tables.

And if you do decide to move to a INT surrogate, please remember to place a unique constraint on these columns still - they are a real key for these tables.

I would use INT ids as a key instead of ISO codes and explain you why:

Organization I worked for, uses "own currency" (LBP) - eg, when a user performs some transaction, he receives some amount of LBP as a bonus. Further, he can exchange those LBPs to USD, EUR, etc and vice versa, pay for services with LBPs, etc. Also, I didn't find BTC (Bitcoin) currency in ISO standard.

Yes, these are not official currencies, but it is more flexible from system and users point of view to have them as currencies but not as an additional product which user can buy and sell.

Organization I worked for do not use INTS as primary key, they use ISO codes as ids (plus those additional currencies).

Officially, LBP is ISO standard for Lebanese Pound - so they will not able to add Lebanese Pound to the system smoothly.

If you identify your currencies by Code, and in the future some new currency will be registered as ISO standard (say, LBE, or BTC) - then these currencies will conflict with "your" currencies.

Somebody mentioned here that having additional int key for currencies is an additional index. But excuse me, is it a problem for 300 records (approximate count of currencies)? Moreover, if you use INTs as primary key for currencies, it has an additional benefit: Imagine a table with 1M transactions which holds amounts and currencies, and what is more efficient: INTS or CHARS?

So I would go for INTs.

Yes, changing to an integer key would be a good idea before it's too late.

E.g. what if Great Britain joins the Euro-zone?

It is a poor practice to use something as primary key that changes. Suppose the value changed, and then you had to update all child records. Doing so could lock up your database for hours or even days. This is why the integer FK with the unique index on the natural key is a better practice for information that is volatile.