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