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.

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?

You could use a self-referencing foreign key in the State table to make a "parent-child" hierarchy of states.

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?