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

    Question best design for states, countries and customers

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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...)
    UnitName

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

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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