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?