Re: Geographic data sources, queries and questions

"Chuck D." <pgsql-list(at)nullmx(dot)com> writes:

> 1) The first is this. I have 3 tables. Country, state and city. Country has
> a country_id to identify a country, state has a state_id and country_id to
> identify a state, and city has a city_id, state_id and country_id (for easy
> reference) to identify it. I then have a table for users that stores their
> city, state and country ID's along with other info about them.

I don't believe this is good design. You'll have to have a trigger or
something to verify that the country_id+state_id on the city table are
exactly equal to the country_id+state_id on the state table. If you
don't, you might have something like (using US city names...) "country:
USA -> state: NY" -> "country: Zimbabwe -> state: NY -> city: New
York".

It isn't a problem of "any country and any state" on the city table, but
a problem of "this state inside that particular country". I'd drop the
country column.

> My problem came recently when I questioned the integrity of the data and
> needed to make some changes. I thought to myself that maybe storing the ID
> wasn't as good as storing the ISO or FIPS 2 letter abbreviation. The only
> problem the abbreviation could changed at some point by the regulating bodies
> and all rows in all tables would need to be updated.

You have integrity problems because you denormalized your model too much
and tried to attach the same information on two different places without
requiring those to be equal.

> The question is, for the purposes of querying or searching is it better to
> store and search a 2 byte integer that is indexed for country or state ID's,
> or is it better to store and search a 2 byte CHAR abbreviation?

It all depends: surrogate primary keys or ... :-) (old flame starter)

--
Jorge Godoy <jgodoy(at)gmail(dot)com>