Re: Geographic data sources, queries and questions

[Oliver Elphick <olly(at)lfix(dot)co(dot)uk>]

On Tue, 2007-05-29 at 13:49 -0500, Michael Glaesemann wrote:
> 
> If you're handling more than one country, you'll most likely want to  
> associate the states with their respective countries.
> 
> -- Listing 4
> CREATE TABLE countries
> (
>      country_id INTEGER PRIMARY KEY
> );
> 
> CREATE TABLE states
> (
>      state_id INTEGER PRIMARY KEY
>      , state_name TEXT NOT NULL
>      , country_id INTEGER NOT NULL
>          REFERENCES countries (country_id)
> );
> 
> Note that there's no UNIQUE constraint on state_name. You may have  
> more than one state with the same state_name around the world so you  
> may want to make sure that for each country, each state_name is
> unique:

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

-- 
Oliver Elphick                                          olly(at)lfix(dot)co(dot)uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html