Re: Geographic data sources, queries and questions

On Wednesday 23 May 2007 18:59, you wrote:

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

You are right, this is a bad design.  The country_id on the city table has to
go.