Re: Geographic data sources, queries and questions

>>> Even ISO country codes are not guaranteed to be stable
>>
>> I'm not sure where the idea that primary keys must be stable comes
>> from. There's nothing necessarily wrong with updating a primary
>> key. All a primary key does is uniquely identify a row in a table.
>> If that id changes over time, that's fine, as long as the primary
>> key columns continue to uniquely identify each row in the table.
>
> And any archived data (for example, transaction detail that you
> must keep for 7 years but don't still want in your database, since
> it doubles your backup/restore times) will still have the old codes.
>
> "Static" data needs to be static.

Yes, and then there is the question of what such a recycled code
actually =means= as a foreign key.

For example, CS used to be the code for Czechoslovakia, then it was
for Serbia and Montenegro, now it is in "transition" before being
deleted. Czechoslovakia no longer has a code, since it no longer
exists, as far as ISO is concerned. What do you want to do with your
biography database for 19th century Slavic poets, which indicate that
some people were born in Czechoslovakia. Did those people move
(briefly) to Serbia and Montenegro? Or did their birthplace change
to NULL? If you want to give them a code, you have to find out what
part of Czechoslovakia they actually lived in, and what country that
region's now in. Do you really want some external agency forcing you
to muck with you data like this?

Anyway, regardless of one's feelings along these lines, I thought
many might be implicitly assuming that all of these standards
guarantee such stability, and I wanted to disabuse folks of that.

- John Burger
MITRE