Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2013

    Question best design for states, countries and customers


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

  2. #2
    Join Date
    Jun 2003
    You need an adjacency table (recursive) for geographic units.

    table GeographicUnit
    ID int not null
    ParentID int null (Recursive foreign key to GeographicUnit.ID)
    UnitType ("State", "Country", "Province", etc...)

    Then you can assign a location at any level.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts