Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    29

    Country-State-Town table design

    Hi Forum,

    As a part of a database I am designing, I am trying to use the ISO-3166 standard to develop a listing of Countries, States [or equivilant] and Towns or Suburbs.
    The standard is listed in Wikipedia ISO 3166-1 - Wikipedia, the free encyclopedia.

    When I started, I thought it would be a simple matter of three [3] tables being:

    1) Country : ISO Code, Country Name
    2) State : StateID, Country ISO Code, State ISO Code, State Name
    3) Town : StateID, Town Name.

    However, when assembling the tables, I discovered that some countries. eg. Bangladesh, Cape Verde etc have two sub-divisions, or two states, while most only have one.

    Adding a second State table does not seem to work, and adding an additional column to the State table for these countries would mean adding the first state in multiple times?


    Can anyone suggest the best way to deal with the second subdivision?

    Any comments or suggestions would be greatly appreciated.

  2. #2
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    The answer will depend in part on what you are intending to use the tables for. Are these for mailing addresses, a geographical database, or what?

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by Wedgetail View Post
    Can anyone suggest the best way to deal with the second subdivision?
    You could use a self-referencing foreign key in the State table to make a "parent-child" hierarchy of states.

  4. #4
    Join Date
    Feb 2009
    Posts
    29
    This is part of a much larger database that I am slowly designing for an engineering consultancy.

    I was intending on using it as base information for:
    1) Addressing/mailing information for Companies/People.

    2) A component of a geographic information system, so that each could ultimatly have both a polygong and point data associated with them.

    3) A component of the Technical / Library system, as each would generally be an administrative area, which would have different standards, technical requirements for different elements of work.

    As yet, I have not thought much about the Technical / Library component, other than that we have some information that relates only to specific areas.
    eg. Australian Standards, UK Standards etc.

    In addition, there can also be more than one group.
    eg. An address may be
    Country : State : Town/Suburb : Postcode
    Australia - NSW - Sydney - 2000.

    However, in terms of Land Titles
    County : Parish : Town/Suburb
    Cumberland : St Philip : Sydney

    Finally, any developement etc falls under the jurisdicition of the Local Council.
    Local Council : Town/Suburb
    Sydney City Council : Sydney

    Since my original post, I found a similar question that suggested using a Single Region table for Countires, State, Town. in the following form.

    Region Table
    ---------------------
    regionID (PK)
    RegionType (Country, State, Town)
    Name
    parentRegionID

    However, in what I am trying to do, I thought maybe that this could be modified into two tables, given that there is not a 1-1 relationship for it all. Something along the lines of:

    Region Table
    ----------------------
    regionID (PK)
    ISO3166 Code
    RegionType (Country, State, Town\Suburb, County, Parish, Council)
    RegionName
    RegionEntityID (Link to other area of Database: Link to company information. eg. Government Deparment, Local Council)
    AddressRegion (Boolean, True or False)

    Region Relationships
    -----------------------
    regionRelationshipID (PK)
    RegionID
    parentRegionID
    RelationshipType (Address, Land Title, Local Council)

    Is this an appropriate appproach, or should I be approaching this another way?

Posting Permissions

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