Trees & Hierarchies in SQL

[Anon]

Hi,

I was hoping someone could help me with a table design issue. What I would like to create is something that will have a Country, Province, City and then a list of all the Regions (specific area's of the city.. example: West End).

So an example table design follows:

Create table locations (
Id auto increment
Country
Province
City
Region
);

Some example records would look like this.

1, Canada, Alberta, Calgary, North East
2, Canada, Alberta, Calgary, North West
3, Canada, Alberta, Calgary, South East

I know this is a bad idea for table normalization but what is a better way to build a Trees & Hierarchies in SQL??

I want to create a list of all the cities across Canada. So there will be MANY records!

• Justin

[edwardsbc]

Well, if you really want to normalize the DB you should have a table for country, a table for province, a table for city, and a table for region. Each table will have a foreign key to the table above it. Of course, if you aren't familiar with SQL Joins you may find retreiving the information a bit daunting. But to minimize the data stored (I'm assuming you want more than just the four fields you show above) this is the way to go.

If you really are just using the four fields, then normalizing may not be necessary. You could be perfectly happy with the structure you've shown.