Hi

i always wanted to know what is the best database design to link customers, countries and states tables/entities.

several countries have states (e.g. USA)
if a customer is located in 1 country, he can (or not) live in a state also.

therefore how are those 3 entities related to each other.
because:
- if table "countries" has a primary key "country_id"
- if table "states" has a primary key "state_id"

as we know a state belongs to only 1 country, therefore in table "states", we should have a foreign key redirecting to table "countries" (countries_id - primary key).

now a customer is located in a country (and maybe in a state).
therefore in table "customers" we should have 2 foreign keys (states_id and countries_id)

but in this case all three tables have recycling relationships

any idea how this situation should be solved ?
thx