Re: basic geographic structure

What about a self-referencing table?

Region
----------
region_id
country_id
parent_region_id
PRIMARY KEY ("region_id")
FOREIGN KEY ("parent_region_id") REFERENCES "Region" ("region_id") ...


--
Brandon Aiken
CS/IT Systems Engineer
________________________________________
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Dario Borda
Sent: Friday, September 15, 2006 2:29 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] basic geographic structure

Hello

I need help with the data model of a basic geographic structure
Country > State/Region > Sub-Region

Most of the countries have a 3 level, so i want to do this
_________
Country
contry_id
...
_________
Region
region_id
country_id
....
__________
Sub-region
sub_region_id
region_id
_________
Client
client_id
sub_region_id

My Question:

Some countries do no have 3rd level (Sub-region) what is the best way to
manage this when normaly my Client table needs to be linked to the 3er
level data (sub_region_id)

Thank you, any tips will be appreciated

Dario




________________________________________
Stay in the know. Pulse on the new Yahoo.com. Check it out.