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.