Re: Geographic data sources, queries and questions

Oliver Elphick wrote:

> You have assumed that state codes are unique integers, but for a
> worldwide database that is probably a bad design. The USA knows its
> states by two-letter codes, as does India and one should surely not
> invent a new set of codes for them. I would make this field a
> VARCHAR(3) with an upper-case constraint.

In fact, the US postal codes are not what most govt. data sources use
- they are mandated to use FIPS codes, which are numeric and are not
guaranteed to be stable!!!

> Furthermore, these codes are
> not going to be unique. For instance MH is the US abbreviation for
> the
> Marshall Islands [US Post Office] and also the Indian abbreviation for
> Maharashtra [Wikipedia]. In such a case I would always make the
> country
> code part of the primary key and not just an attribute. Again this
> saves your having to invent a new set of codes when one exists
> already.

Even ISO country codes are not guaranteed to be stable - I think
Yugoslavia is one example where a code has been recycled recently.
As I said, we found the simplest approach was to use our own internal
IDs for these things, and have a table mapping these to the codes
used in various standards.

- John D. Burger
MITRE