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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
