Re: Geographic data sources, queries and questions

On 05/30/07 11:01, John D. Burger wrote:
>>>> 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?

In situations like this (a toll road schedule) we add beginning and
expiring timestamps, and the expiring timestamp is part of the
natural PK.

> 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.

It's very interesting and useful to know.

--
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!