Re: Geographic data sources, queries and questions

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